If you’re waiting for an easy way to load data from one or more Teradata table(s) into a Teradata table without writing a Teradata PT script, wait no further. Teradata PT Easy Loader can do it easily. In the 14.0 release, the tool can load data from a Teradata table or from SELECT statement(s).
You can enter the necessary options on the command line; but, this article will mainly show you how to specify them in a job variable file. Mistakes can be easily made while typing a long command with many options and values. Plus, it can be easier to modify a job variable file to accommodate different load jobs when needed.
After you create a job variable file, you can execute the simple command with the option
tdload -j job-variable-file-name
If you are not executing the
tdload command in the directory where the job variable file is located,
job-variable-file-name must be a full qualified filename.
You can use any text editor to create a job variable file that contains all the necessary options and their corresponding values to be used with Teradata PT Easy Loader. Each job variable must be defined on a single line separated by commas, using the following format:
option = value [,]
To load data from a table, Teradata PT Easy Loader must know:
Such information can be defined in a job variable file using the following options:
SourceTable = ‘sourceTableName’,
SourceTdpId = ‘sourceTdpid’,
SourceUserName = ‘sourceUserName’,
SourceUserPassword = ‘sourcePassword’,
TargetTable = ‘targetTableName’,
TargetTdpId = ‘targetTdpid’,
TargetUserName = ‘targetUserName’,
TargetUserPassword = ‘targetPassword’
If the source table resides in a different database than the default database of the specified Teradata user, you must add the
SourceWorkingDatabase option to the file, as follows:
SourceWorkingDatabase = ‘sourceWorkingDatabase’
If the target table resides in a different database than the default database of the specified Teradata user, you must add the
TargetWorkingDatabase option to the file, as follows:
TargetWorkingDatabase = ‘targetWorkingDatabase’
If the source data comes from multiple tables as the result of one or more
SELECT statements, the job variable file should, at least, have the following:
SelectStmt = ‘selectRequest’
The two options
SelectStmt cannot be used simultaneously. Teradata PT Easy Loader will not terminate if both options are specified; instead, it will ignore the option
SourceTable and extract data based on the value specified in the option
There are a few restrictions on the
SELECT request specified in the option
SELECT statement cannot:
SELECT DATE;is invalid statement.
SELECT DATE, col1, col2, col3 FROM source_table;
SELECTstatement must have a
BLOB(Binary Large Object) or
CLOB(Character Large Object) data type.
Since the SELECT statement can have space/blank characters, the entire statement must be enclosed in double quotation marks (
") when specified on the command line, as follows:
tdload --SelectStmt "select * from src_tbl;"
If you prefer to specify all options on the command line, the following command will load data from a Teradata Database source table:
tdload --SourceTable sourceTableName --SourceTdpid sourceTdpid --SourceUserName sourceUserName --SourceUserPassword sourcePassword --TargetTable targetTableName --TargetUserName targetUserName --TargetUserPassword targetPassword --TargetTdpid targetTdpid
The following command will load data from a
tdload --SelectStmt “sel * from src_tbl;” --SourceTdpid sourceTdpid --SourceUserName sourceUserName --SourceUserPassword sourcePassword --TargetTable targetTableName --TargetUserName targetUserName --TargetUserPassword targetPassword -TargetTdpid targetTdpid
Since Teradata PT Easy Loader offers short options (
-h, -u, and
-t) to define a Teradata Database target table, you can enter the following command:
tdload --SelectStmt “sel * from src_tbl;” --SourceTdpid sourceTdpid --SourceUserName sourceUserName --SourceUserPassword sourcePassword -t targetTableName -u targetUserName -p targetPassword -h targetTdpid
How to mention different File Names in Job variable file in a single TDload command!.
I believe we can load more than 2 files into a single table using Wildcard character. I am unable to identify the character.
I have 2 files, say S01.txt and S02.txt..
what is the format that we need to declare in the "Filename" variable.