How to use Teradata Import Script Generator ?
This tool helps you generate BTEQ Import, Fastload, Multiload and TPT Load script for any table. Few inputs required from user are :
- SERVER IP: Enter TDPID or Teradata server IP to connect. Default value is ‘DummyIP’
- USERNAME: Enter username required to connect to TD. Default value is ‘DummyUser’
- PASSWORD: Enter password required for username. Default value is ‘DummyPassword’
- TABLENAME: Enter TABLE name along with DATABASE name. Default value is DummyDb.DummyTbl
- FILENAME: Enter data file name with path. Default value is ‘Filename.txt’
- Max String Length: This should be set to maximum string length for any column which you are passing. Default value is ‘varchar(400)’
- Delimiter: Pass the delimiter used in the file. Default value is ‘|’
- Column list: Enter column names from the table to which data file is to be imported. Enter one column name per line.
- There are 4 buttons available at the bottom namely : BTEQ, Fastload, MultiLoad, TPTLoad. Click on one or all button to generate respective import Scripts.
Teradata Import Script Generator - Utility
To fetch Columnname , use below options:
Option 1: select columnname from dbc.columnsV where databasename='DummyDb' and tablename='DummyTbl' order by ColumnID
Option 2: help table DummyDb.DummyTbl and copy columnnames only
Click Button below:
Save content in a file. eg: filename
To execute the script use below commands:
- bteq < filename
- fastload < filename
- mload < filename
- tbuild -f filename
Few assumptions made to keep things simple here:
- Dummy values are mentioned above. If you are not giving any input then please remember to update the “Dummy” values with actual before executing the script.
- Column list is mandatory and user have to provide one column per line. To fetch the column list, 2 options are also mentioned above. Either by running SQL query or by running HELP command. Copy and paste the column list in the textbox. Example, you want to give 4 columns of EMPLOYEE table then you will pass values like:
- FILENAME: You can pass absolute data file path as well. However for TPT Load , you may want to use DirectoryPath variable to set path.
- Once code is generated and “dummy” value (if applicable) is modified, save the content into a file and name it as per your wish.
- To execute the code , use respective command mentioned below:
bteq < filename
fastload < filename
mload < filename
tbuild -f filename
The inspiration behind creating this utility is I get so many request regarding loading data files into Teradata tables. So the people who are not usual Teradata developer however they have basic understanding of SQL , often struggle to write these scripts. Also writing scripts is not complex however it may take some time. Hence to save time and help others who just want some data into Teradata table I have created this utility. You may want to change some variables as per your requirement. The code above is primarily to load empty tables. Hope it helps.
Check the demo video of Teradata script generation below: