Monday, August 23, 2010

Export /Import using Teradata SQL assistant

please Use this option  for few reocrds only (say in 1000's) . For large data volumes use fastload (empty table loading) or Mload utilities



1. Login to the Source Database using Teradata SQL Assistant. ( QA / PROD)

2. Paste the Source SQL Query in the "Query" window.

3. Select the following Menu Option "File=>Export Results"



















4. Go to "Tools=>Options" and select the Date Format as 'YYYY-MM-DD'.
P.S:  This option is used when a date column exists in the table. 

















5. Run the Query (Press - F5) and Provide the Target Location where you want the output file to be saved:


















6. Open the Saved File (ex. Test_Export.txt) and remove the First Line from the File Which contains all the column Names.

7. Login to the Target Database using Teradata SQL Assistant (Dev).

8. Select the following Menu Option "File=>Import Data"





















9. Paste the SQL Insert Query in the "Query" window.
ex. Insert into TBENW_RVLTN_REPL_LOC_SKU_FW values (?,?,?,?,?);

NOTE: There should be a '?' as a Place-Holder for all the columns in your Source File.

10. Run the Query (Press - F5).

11. Turn off the Import Data mode and resume querying.




Importing Excel Data into Teradata Table

1. Make sure you have the columns in Excel in the same order as the table you created. Remove the header rows from the Excel file and Save the file as comma delimited file from Excel (CSV File).
2. In SQL Assistant set the Delimiter as ","(comma) .
( Delimiter is available in Menu Tools , Options, Import/Export)

















3. Go to "Tools=>Options" and select the Date Format as 'YYYY-MM-DD'.
P.S:  This option is used when a date column exists in the table. 

















4. From Teradata SQL Assistant, click 'File', then 'Import Data'.

5. Run the command: insert into TABLENAME values (?,?,?....) 
You must supply a ? (question mark) for every column. So, for a table with 5 columns the values command would look like: values (?,?,?,?,?).

6. Highlight the insert query and Press F5. This will prompt you for a file location. Select the appropriate file.

7. This will load in the order in which they appear in your text file, which is why it is important to match the columns in Excel with the order of the columns in your table.

8. Turn off the Import Data mode and resume querying.

11 comments:

  1. Kudos to you for the detailed explanation..i just started loving this site..

    ReplyDelete
  2. I have some embedded new line characters in one of the columns in Excel. Is there a way to load it?

    ReplyDelete
  3. Hi,
    is there any SQL-Statement in Teradata to export the results like "export outfile "O:\..." ?
    BR
    JB

    ReplyDelete
    Replies
    1. if you find one, please post...

      Delete
  4. Thanks..It helpd alot

    ReplyDelete
  5. Can I use the same function to load data into a populated table in which I am not using all fields???

    ReplyDelete
    Replies
    1. yes , you can skip some fields .

      But those fields/columns which are not part of import should be "NULLABLE" columns.

      Delete
  6. Hi, can I use same steps for updating records in database from a file.I have a uniuqe number (Primary key)in a table to compare. I have total of 2000 records in a table and 200 records in a file with 2 columns updated values.

    short, can we use import wizard for Updates?
    if not is there any other procedure to automate updates based on one column (primary key)?

    Thanks

    ReplyDelete
    Replies
    1. Hi,

      It is not possible to update values using above mentioned method.

      But you can try out this method
      BTeq script - do Bteq import into empty table and use "MERGE INTO" sql statement

      Br,
      Vinay SHet

      Delete
  7. HI,

    I tried the same but I have Date Time & Date columns in my Table & when importing the data it gave me an error :
    Error executing query for record 1 : 1204 : Invalid year in date

    Please help resolve this.

    The two data types for columns related to date are :
    DATETIME YEAR TO SECOND & DATE

    ReplyDelete
    Replies
    1. please use casting to varchar in select statement and make sure to set delimit to ";"

      Create the table as with columns as VARCHAR and import them. Then you can create copy of table in Teradata with datatype Dates.. .

      Hope it helps,

      Br
      Vinay SHet

      Delete