If you are using Teradata SQL Assistant and want to import data into the Teradata environment, you would glad to know that there is a Import Utility available for it. How to use this import utility? Simply, follow the step by step instructions below.

Situation – For example you have a list of “account numbers” on your Personal Computer or Local Machine which you want to use to retrieve rows from a table, say Account Details, present in Teradata Database. File is (has to be) in txt format and the name of the file is “acct_no.txt”.

Step 1. First create a volatile table to import the data present in the txt file:

CREATE VOLATILE TABLE ACCOUNT_NO

(ACCTNO INTEGER)

ON COMMIT PRESERVE ROWS;

Thing to note about volatile table is that it is created in spool space.  It goes away once you log off SQL Assistant.

Step 2. Import the data into the table you created.  Initiate the Import Process by clicking on ‘File’ menu and then selecting the menu item ‘Import Data’. If you have already have a Query Window open then the Import Data utility would not open another one and you can use the current window to insert records, just make sure that the query window is BLANK. Or else you might get some syntax errors. In the BLANK query window, enter the following query

INSERT INTO ACCOUNT_NO

(?);

The “?” is a parameter marker and indicates that you are importing values into the first column in the table. When you run this query, you will be prompted with the option to select the input file from your local computer. Select the file “acct_no.txt”.

Importing process would start and you can see the status in the left corner of the status bar.

Step 3. Once the file is imported, turn off the import function by clicking on File menu and selecting the menu item ‘Import Data‘ again.

Step 4. Now you can use the data present in the volatile table in a sub query to select rows from the ACCOUNT_NO table.  Here is an example of how you would do that:

SELECT ACCT_NAME, ACCT_HOLDERS_NAME, ACCT_ADDR

FROM ACCOUNT_DETAILS

WHERE MASTACCT_KEY IN

(SELECT ACCTNO FROM ACCOUNT_NO);

If you have more than one column to import, then first set the column delimiter in the SQL Assistant by selecting the Tools > Options.

Click the Export/Import tab and Choose from the following options

Use this delimiter between columns in files - defines the column delimiter. The default value is Tab. This same character is expected to delimit the data fields in an Import file. If you have comma as the delimiter then choose the same from the drop down menu. Come back to query window and change the INSERT command to the following based on the number of columnsINSERT INTO ACCOUNT_NO(?,?); Note the following points while using parameter markers:

• ? - The data for this parameter is read from the Import file. It is always a character string, and will be converted to a numeric value by the database if necessary.
• ?? - The data for this parameter resides in a file that is in the same directory as the Import file. The import file contains only the name of the file to be imported. The contents of the file are loaded as a binary image.
• ?B - This is identical to the ?? marker.
• ?C - The data for this parameter resides in a file that is in the same directory as the import file. The import file contains only the name of the file to be imported. Use this marker to load a text file into a CHAR or CLOB column.

• A NULL character is inserted just by having two commas with a space (?, ,?). Here the 2^nd column is a NULL column
• A constant can also be inserted by mentioning the word in single quotes i.e. ‘constant’.

Popularity: 33% [?]

Our Random Articles

• How To Choose A Good Online Traffic School
• New Year Wishes 2010
• New Year Wishes
• How to use EXTRACT function with date and time columns
• How to Post on a Separate/New Page in WordPress

More Links