If you want to import data to a table in Teradata environment, BTEQ utility can help you here. There are a couple of ways you can import data using BTEQ. One way is to logon to BTEQ environment and type the import commands one after another. The other way is to put everything in a script file and run the same either from a BTEQ environment (using .run command) or from DOS environment using BTEQ command. Here we will take the example of running the script from DOS prompt.

1. Open a txt file and write the following with valid replacement values (where you see <>),

.logon <server name>/<user name>, <password>;

SET session dateform=ansidate;

DATABASE <database name>;

SEL date;–to select current date

SEL time;–to select current time

CREATE VOLATILE TABLE <table name>

          (Column1 CHAR (10),

           Column2 CHAR (10))

ON COMMIT PRESERVE ROWS;

select count(*) from <table name>; — To check the count before insert.

.import report file=C:\<Mypath>\<MySubpath>\<MyDir>\<MyDatafile>;

.quiet on       – remember it is “quiet” and not quite this is to limit output reporting

.repeat*        – used with import to read until EOF is reached.

USING (col1 char(8), col2 char(8))

INSERT INTO <table name> (Column1, Column2)

values (:col1, :col2);

.quiet off       – used to print output to logfile

select count(*)from <table name>;

.QUIT ERRORCODE ;

2.  Save the file as MyImportfile.txt

3.     Run/Execute the MyImportfile.txt from DOS (from NCR dir) as follows;

bteq < MyImportfile.txt > MyImportfile.log

The sample data inside MyDatafile looks like follows;

11111111 2222222

33333333 4444444

55555555 6666666

77777777 8888888

99999999 1010101

12121212 13131313

14141414 15151515

16161616 17171717

18181818 19191919

20202020 21212121

There is a space in between the 1st data and the 2nd data column.

Our Random Articles

More Links