Advantage of Load-Template is that it eliminates the need to define separate TPT script for each of the table.

TPT Load-Template

TPT Load-Template is a file consumer which works similar to Fastload utility. Advantage of Load-Template is that it eliminates the need to define separate TPT script for each of the table.

• Delimited
• Unformatted
• Formatted
• Binary
• Text

1. Seven basic attributes are mandatory( that are UserName, UserPassword, SelectStmt, Tdpid, FileName, Format and TargetTable) for using TPT Load-Template.
2. TPT Load-Template eliminate the need of defining 'OPERATOR' separately.
3. There is no need to define schema as Load-Template generates schema from the target table which can be passed as variable.
4. Since the Load-Template requires table to be empty, data is first removed from the table if present.
5. Main difference between Load-Template scripts for 'DELIMITED' and other Formats is the presence of DELIMITED keyword in front of target tablename.
6. Careful! always define maximum number of sessions(using MaxSessions) that can be allocated to the job, else it may end up taking all the sessions.

• Sample Script 1: TPT Load-Template script(tpt_load_template_delimited.tpt) can be used to load data delimited file.

  #tpt_load_template_delimited.tpt
  USING CHARACTER SET @var_utf 
  DEFINE JOB tpt_load_template 
  (
    STEP DELETE_DATA_FROM_TABLE(
    APPLY('DELETE FROM ' || @var_tablename ) TO OPERATOR ($DDL() ATTR (TdpId=@var_tdpid, UserName=@var_userid, UserPassword=@var_password));
    );
    STEP LOAD_DATA_INTO_TABLE (
    APPLY $INSERT TO OPERATOR ($LOAD() ATTR (TdpId=@var_tdpid, UserName=@var_userid, UserPassword=@var_password, TargetTable=@var_tablename, MaxSessions=@var_maxsessions, DateForm = @var_dateform))
    SELECT * FROM OPERATOR($FILE_READER(DELIMITED @var_tablename) ATTR (DirectoryPath = @var_directorypath,FileName=@var_inputfile, Format=@var_format, TextDelimiter=@var_delimiter_value, OpenMode = 'Read'));
    );
  );
  

  Click here to download the above Load-Template script for delimited file.
  
  
• Sample Script 2: TPT Load-Template script(tpt_load_template.tpt) can be used to load data from file of any format(other than delimited).

  #tpt_load_template.tpt
  USING CHARACTER SET @var_utf 
  DEFINE JOB tpt_load_template 
  (
    STEP DELETE_DATA_FROM_TABLE(
    APPLY('DELETE FROM ' || @var_tablename ) TO OPERATOR ($DDL() ATTR (TdpId=@var_tdpid, UserName=@var_userid, UserPassword=@var_password));
    );
    STEP LOAD_DATA_INTO_TABLE (
    APPLY $INSERT TO OPERATOR ($LOAD() ATTR (TdpId=@var_tdpid, UserName=@var_userid, UserPassword=@var_password, TargetTable=@var_tablename, MaxSessions=@var_maxsessions, DateForm = @var_dateform))
    SELECT * FROM OPERATOR($FILE_READER(@var_tablename) ATTR (DirectoryPath = @var_directorypath, FileName=@var_inputfile, Format=@var_format, TextDelimiter=@var_delimiter_value, OpenMode = 'Read'));
    );
  );
  

  Click here to download the above Load-Template script.

• Load Delimited File: Executing delimited TPT Load-Template script and load data from delimited file.

  tbuild -f tpt_load_template_delimited.tpt \
  -u " var_tdpid='TDSERVER', \
  var_userid='tutorial_user', \
  var_password='tutorial_user', \
  var_inputfile='export_template_outfile.txt', \
  var_directorypath='/path_to_input_file', \
  var_delimiter_value='|*|', \
  var_tablename='tutorial_db.employee',
  var_utf='UTF8', \
  var_maxsessions=4, \
  var_dateform='ANSIDATE' \
  var_format='DELIMITED'"
  

  
  
• Load Unformatted File: Executing non-delimited TPT Load-Template script and load data from 'UNFORMATTED' format file.

  tbuild -f tpt_load_template.tpt \
  -u " var_tdpid='TDSERVER', \
  var_userid='tutorial_user', \
  var_password='tutorial_user', \
  var_inputfile='export_template_outfile_unformatted.txt', \
  var_directorypath='/path_to_input_file', \
  var_tablename='tutorial_db.employee',
  var_utf='UTF8', \
  var_maxsessions=4, \
  var_dateform='ANSIDATE' \
  var_format='UNFORMATTED'"
  

  
  
• Load Formatted File: Executing non-delimited TPT Load-Template script and load data from 'FORMATTED' format file.

  tbuild -f tpt_load_template.tpt \
  -u " var_tdpid='TDSERVER', \
  var_userid='tutorial_user', \
  var_password='tutorial_user', \
  var_inputfile='export_template_outfile_formatted.txt', \
  var_directorypath='/path_to_input_file', \
  var_tablename='tutorial_db.employee',
  var_utf='UTF8', \
  var_maxsessions=4, \
  var_dateform='ANSIDATE' \
  var_format='FORMATTED'"
  

  
  
• Load Binary File: Executing non-delimited TPT Load-Template script and load data from 'BINARY' format file.

  tbuild -f tpt_load_template.tpt \
  -u " var_tdpid='TDSERVER', \
  var_userid='tutorial_user', \
  var_password='tutorial_user', \
  var_directorypath='/path_to_input_file', \
  var_inputfile='export_template_outfile_binary.txt', \
  var_tablename='tutorial_db.employee',
  var_utf='UTF8', \
  var_maxsessions=4, \
  var_dateform='ANSIDATE' \
  var_format='BINARY'"