Teradata TPT - Export & Import Script Examples - Download them for free!

Teradata TPT – Export & Import Script Examples

teradata tpt

The following two Teradata TPT scripts show how easy it is to export a table from Teradata and upload it for example to another Teradata system.

Please note that bulk loading is used in this example. So there should be more than a few hundred thousand rows loaded this way. Few rows should only be loaded with transactional load methods (BTEQ, TPUMP) to conserve resources.

In this example, an export file in Teradata format “unformatted with indicators” is created.

Teradata TPT Export Script

USING CHARACTER SET UTF8
DEFINE JOB EXPORT_DELIMITED_FILE
DESCRIPTION 'Export rows from a Teradata table to a unformatted file'  (  DEFINE SCHEMA FILE_SCHEMA  (
"COL1" CHAR(6)
,"COL2" VARCHAR(66)
,"COL3" DECIMAL(4,0)
,"COL4" INTEGER
);  
DEFINE OPERATOR SQL_SELECTOR 
TYPE SELECTOR  SCHEMA FILE_SCHEMA ATTRIBUTES  
(
  VARCHAR PrivateLogName = 'selector_log',  
  VARCHAR TdpId = 'TD_SERVER', 
  VARCHAR UserName = 'dwhpro',  VARCHAR UserPassword = 'xxxxxx', 
  VARCHAR SelectStmt = 'SELECT * FROM DWHPRO.SourceTable;'  
);  
DEFINE OPERATOR FILE_WRITER TYPE DATACONNECTOR CONSUMER SCHEMA *  ATTRIBUTES  
(  
  VARCHAR PrivateLogName = 'dataconnector_log', 
  VARCHAR DirectoryPath = './data/',  
  VARCHAR FileName = 'test.txt',  
  VARCHAR Format = 'UNFORMATTED',  
  VARCHAR IndicatorMode = 'Y',  
  VARCHAR OpenMode = 'Write'
);  
APPLY TO OPERATOR (FILE_WRITER)
SELECT * FROM OPERATOR (SQL_SELECTOR);  
);

Teradata TPT Import Script

USING CHARACTER SET UTF8
DEFINE JOB IMPORT_DELIMITED_FILE  DESCRIPTION 
'Import rows to a Teradata target table'  
(  
  DEFINE OPERATOR Consumer_Table_Detail TYPE LOAD SCHEMA * ATTRIBUTES 
  (  
   VARCHAR TdpId = 'TD_Server',  
   VARCHAR UserName = 'dwhpro',  VARCHAR UserPassword = 'xxxxxx',  
   VARCHAR TargetDatabase = 'DWHPRO',  
   VARCHAR TargetTable = 'DWHPRO.TargetTable',  
   VARCHAR LogTable = 'target_table_log',  
   VARCHAR DateForm = 'AnsiDate'
  ); DEFINE SCHEMA Define_Target_Schema   
(
"COL1" CHAR(6)
,"COL2" VARCHAR(66)
,"COL3" DECIMAL(4,0)
,"COL4" INTEGER
);  

DEFINE OPERATOR Producer_File_Detail TYPE DATACONNECTOR PRODUCER SCHEMA Define_Target_Schema ATTRIBUTES 
(  
  VARCHAR DirectoryPath = './data',  
  VARCHAR Filename = 'test.txt',  
  VARCHAR Format = 'UNFORMATTED',  
  VARCHAR OpenMode = 'Read',  
  VARCHAR IndicatorMode = 'Y',  
  VARCHAR DateForm = 'AnsiDate'
); 
APPLY ('INSERT INTO DWHPRO.TargetTable(
COL1
,COL2
,COL3
,COL4
) VALUES (
:COL1
,:COL2
,:COL3
,:COL4
); ') 
TO OPERATOR (Consumer_Table_Detail) 
SELECT * FROM OPERATOR (Producer_File_Detail););

If you want to get to know all functionality of Teradata TPT please read on here:

DWH Pro Admin
 

>