ETL SQL

Learn Extract Transform Load using SQL & PySpark

Generate Teradata scripts automatically

Generate Teradata Import Scripts with just one click – saves ~99% man-hours

Step 1

Enter required details of the file/utilty

Step 2

Click on required utility button

Step 3

Copy load script into file & run it

Check the video to see how to use this utility – Teradata Import Script Generator.


Click a button below:

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

Save content in a file. eg: filename

To execute the script use below commands:

  1. bteq < filename
  2. fastload < filename
  3. mload < filename
  4. tbuild -f filename

How to use this Teradata Import Script Generator online ?

This tool helps you generate BTEQ Import, Fastload, Multiload and TPT Load script. Few inputs required from user :

  1. SERVER IP: Enter TDPID or Teradata server IP to connect. Default value is ‘DummyIP’
  2. USERNAME: Enter username required to connect to TD. Default value is ‘DummyUser’
  3. PASSWORD: Enter password required for username. Default value is ‘DummyPassword’
  4. TABLENAME: Enter TABLE name along with DATABASE name. Default value is DummyDb.DummyTbl
  5. FILENAME: Enter data file name with path. Default value is ‘Filename.txt’
  6. Max String Length: This should be set to maximum string length for any column which you are passing. Default value is 400 so columns will be ‘varchar(400)’
  7. Delimiter: Pass the delimiter used in the file. Default value is ‘|’
  8. Column list: Enter column names from the table to which data file is to be imported. Enter one column name per line.

Assumptions

Few assumptions made to keep things simple here:

  1. 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.
  2. 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:
    EMP_ID
    EMP_Name
    EMP_Age
    Emp_Salary
  3. FILENAME: You can pass absolute data file path as well. However for TPT Load , you may want to use DirectoryPath variable to set path.
  4. Once code is generated and “dummy” value (if applicable) is modified, save the content into a file and name it as per your wish.
  5. 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.


Leave a Reply

Your email address will not be published. Required fields are marked *