fbpx

The following two Teradata TPT scripts show how easy it is to export a table from one database and upload it to another database or even Teradata system.

In our example, we use bulk loading, which should be used only for larger tables with at least a few hundred thousand rows. Small tables should be loaded with transactional load methods (BTEQ, TPUMP) to save resources.

In the below 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:

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

DWH Pro Admin

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>