How to Export and Upload Teradata Tables with TPT Scripts: A Guide

These Teradata TPT scripts demonstrate the effortless process of exporting a table from one database and transferring it to a different database or even a separate Teradata system.

We suggest utilizing bulk loading in our example for larger tables with at least several hundred thousand rows. To conserve resources, we advise using transactional load methods (such as BTEQ or TPUMP) for smaller tables.

The following example creates an unformatted export file in Teradata format with indicators.

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););

For a comprehensive understanding of Teradata TPT’s capabilities, please refer to the following guide: