IMPORT Examples - Basic Teradata Query

Example 1 – IMPORT

Use the following command to read from a data set defined by NEWDAT, with an expected data format of INDICDATA.

.IMPORT INDICDATA DDNAME=NEWDAT

Example 2 – IMPORT

Import data records from the same import file can be remapped as needed to accomplish updates to tables that each have differing row layouts.

.import data file = test.data;
using (c1 integer, c2 integer)
insert into table1 (c1, c2)
values (:c1, :c2);
   
using (c1 integer, c2 integer)
insert into table2 (c1, c3)
values (:c1, :c2);
   
using (c1 integer)
update table3 set c1 = :c1 where c2 = :c1;

Note that the import file test.data should contain at least three records so that one record is processed by each of the three requests having a USING clause.

where:

Example 3 – IMPORT

.PACK 3
.IMPORT data file = sample.data;
   
using (x integer, y integer)
insert into table1 (a,b)
values (:x, :y);
   
using (x integer, y integer)
insert into table2 (a,b)
values (:x, :y);

Assume that the import file sample.data contains 10 records. With the PACK factor set to 3 and the value of the REPEAT command being 1, the number of records processed by each of the two requests is three. The result is a total of six records are processed, and the import file has four records remaining.

How using the REPEAT command with the PACK clause affects record processing:

.IMPORT data file = sample.data;
.REPEAT 2 PACK 3
   
using (x integer, y integer)
insert into table1 (a,b)
values (:x, :y);
   
using (x integer, y integer)
insert into table2 (a,b)
values (:x, :y);

Because the value of the REPEAT command is 2 and the value of the PACK clause is 3, six records are inserted into table1 and one record is inserted into table2. Note that the REPEAT command applies only to the request that immediately follows it. The import file has three records remaining.

How using the PACK command and the REPEAT command affects record processing:

.SET PACK 3
.IMPORT data file = sample.data;
.REPEAT 2
   
using (x integer, y integer)
insert into table1 (a,b)
values (:x, :y);
   
using (x integer, y integer)
insert into table2 (a,b)
values (:x, :y);

The PACK command affects both requests containing a USING clause and the REPEAT command affects only the first request. The results are six records are inserted into table1, three records are inserted into table2, and the import file has one remaining record.

Table Definition:
table1 ‘ct table1 (c1 integer, c2 integer)’
table2 ‘ct table2 (c1 integer, c2 integer)’

Example 4 – IMPORT

This example reads data from a file named EXPORT.DAT with an expected data format of DATA, through an Access Module (library name is MYMOD). Note that an init-string is not used.

.IMPORT DATA FILE = 'EXPORT.DAT' AXSMOD MYMOD

Example 5 – IMPORT

The following shows both deferred and non-deferred transfers. If the test5.dat import file contains 3 records, then 9 individual LDO files must also exist (3 files per LDO column).

Since c1 and c2 are always 100 bytes or less, they will always be sent as non-deferred transfers because they will always be less than the specified DEFERLIMITS values. The last column will always be transferred in deferred mode because it is defined with AS DEFERRED BY NAME.

Notice that the DEFERCOLS value only includes the LDO columns defined with AS DEFERRED, and does not include the one LDO column defined with AS DEFERRED BY NAME.

.IMPORT DATA DEFERCOLS=2 DEFERLIMITS=500,800 FILE=test5.dat
.REPEAT *
USING (c1 CLOB(100) AS DEFERRED,
	c2 BLOB(100) AS DEFERRED,
	c3 INTEGER,
	c4 CHAR(10),
	c5 XML AS DEFERRED BY NAME)
INSERT INTO table1 VALUES (:c1, :c2, :c3, :c4, :c5);
.IMPORT RESET

Example 6 – IMPORT

The following shows how the PACK setting can affect the importing of LDOs. Since the import format below is INDICDATA, the TOTCOLS option must be used. Since the DEFERLIMITS option is not specified and the PACK value is greater than one, DEFERLIMITS will be turned off by default, and all LDOs will be sent to the database as deferred transfers.

.PACK 10 
.IMPORT INDICDATA DEFERCOLS=3 TOTCOLS=6 FILE=test6.dat 
.REPEAT * 
USING (c1 CLOB(100) AS DEFERRED,
	c2 CLOB(500) AS DEFERRED,
	c3 CLOB(5000) AS DEFERRED,
	c4 SMALLINT,
	c5 CHAR,
	c6 VARCHAR(10))
INSERT INTO table2 VALUES (:c1, :c2, :c3, :c4, :c5, :c6);
.IMPORT RESET

Example 7 – IMPORT

With z/OS BTEQ, a DDNAME must be defined for the main import data set, but not for individual LDOs. To import LDOs in deferred or non-deferred mode on z/OS, the main import data set must contain actual data set names for each LDO (defined as VARCHAR types). Since the PACK command is being used, the DEFERLIMITS option is included to ensure non-deferred transfers are considered. LDOs that are 500 bytes or less will be imported non-deferred, while all other LDOs will be imported in deferred mode.

//BTEQ 			EXEC PGM=BTQMAIN
//DAT1 			DD DSN=USERID.BTEQ.EXPFILE,DISP=SHR
//SYSPRINT 			DD DSN=USERID.BTEQ.OUTPUT,DISP=MOD
//SYSABEND 			DD SYSOUT=*
//SYSOUT 			DD SYSOUT=*
//SYSTERM 			DD SYSOUT=*
//SYSIN 			DD DATA,DLM=##
.LOGON xxx/yyy,zzz
.PACK 10
.QUIET ON
.IMPORT INDICDATA DEFERCOLS=5 TOTCOLS=7 DEFERLIMITS=500,2000 DDNAME=DAT1
.REPEAT *
USING (
	myclob1 CLOB(300) AS DEFERRED,
	myclob2 CLOB(1000) AS DEFERRED,
	myclob3 CLOB(1000) AS DEFERRED,
	myclob4 CLOB(1000) AS DEFERRED,
	myclob5 CLOB(1000) AS DEFERRED,
	mychar VARCHAR(10),
	myint INTEGER)
INSERT INTO my_table VALUES (:myclob1, :myclob2, :myclob3,
				:myclob4, :myclob5, :mychar, :myint);
.IMPORT RESET
.LOGOFF
.QUIT
##

Example 8 – IMPORT

The following shows how to override the RECORDLENGTH command settings with IMPORT's RECORDLENGTH option.

.RECORDLENGTH=MAX64
/* No override; Using 2-byte record lengths */
.IMPORT DATA FILE=test8a.dat
USING (c1 CHAR(8))
INSERT INTO test8_table VALUES (:c1);
.IMPORT RESET

/* Override record length; Using 4-byte record lengths */
.IMPORT DATA RECORDLENGTH=MAX1MB FILE=test8b.dat
USING (c1 CHAR(8))
INSERT INTO test8_table VALUES (:c1);
.IMPORT RESET

A hex-dump of the test8a.dat file might look like the following:

    08006161 61616161 61610A            **aaaaaaaa*

The test8b.dat file might contain the same column value, but looks slightly different due to the larger record length value:

    08000000 61616161 61616161 0A       ****aaaaaaaa*