Import

Translation reference to convert import MLoad to Snowflake SQL

Description

The IMPORT command specifies a source for data input.

For more information regarding Import MLoad, check here

Sample Source Patterns

As BTEQ content also MLoad is relocated in an EXECUTE IMMEDIATE block. Import transformation with take each layout field an added to a select. Inserts in dml label will be transform to COPY INTO and upserts (Update and insert) will be transform to MERGE INTO.

1. DML label with insert

Teradata MLoad

IN -> Teradata_01.mload

-- Additional Params: -q SnowScript
.LOGTABLE my_table_log;
.LOGON my_teradata_system/username,password;

BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work
ERRORTABLES my_table_err;

.LAYOUT my_layout;
.FIELD col1 * VARCHAR(2);
.FIELD col2 * VARCHAR(5);

.dml label insert_into_my_table
  IGNORE DUPLICATE INSERT ROWS ;
INSERT INTO my_table(col1, col2) VALUES (:col1, :col2);

.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
  FORMAT UNFORMAT 
  LAYOUT my_layout APPLY insert_into_my_table;

.END MLOAD;
.LOGOFF;

Snowflake SQL

OUT -> Teradata_01.sql

EXECUTE IMMEDIATE
$$
  DECLARE
    STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
  BEGIN
    -- Additional Params: -q SnowScript
    /*.LOGTABLE my_table_log;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*.LOGON my_teradata_system/username,password;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*BEGIN IMPORT MLOAD TABLES my_table
    WORKTABLES my_table_work*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '7' COLUMN '1' OF THE SOURCE CODE STARTING AT 'ERRORTABLES'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'my_table_err' ON LINE '7' COLUMN '13'. CODE '81'. ***/
    /*--ERRORTABLES my_table_err*/
     
    /*.LAYOUT my_layout;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
     
    /*.dml label insert_into_my_table
      IGNORE DUPLICATE INSERT ROWS ;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. INFORMATION MAY BE USED IN A TRANSFORMED IMPORT CLAUSE **
     
     
    BEGIN
      CREATE OR REPLACE STAGE &{stagename};
      PUT file://C:\USER\user\my_tr_file_1.tr &{stagename};
       
      COPY INTO my_table (
        col1,
        col2
      )
      FROM
      (
        SELECT DISTINCT
          SUBSTRING($1, 1, 2) col1,
          SUBSTRING($1, 3, 5) col2
        FROM
          @&{stagename}/my_tr_file_1.tr
      );
    END;
    /*.END MLOAD;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
    /*.LOGOFF;*/
    --** SSC-FDM-0027 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.  **
     
  END
$$

2. DML label with upsert

Teradata MLoad

IN -> Teradata_02.mload

-- Additional Params: -q SnowScript
.LOGTABLE my_table_log;
.LOGON my_teradata_system/username,password;

BEGIN IMPORT MLOAD TABLES my_table
WORKTABLES my_table_work
ERRORTABLES my_table_err;

.LAYOUT my_layout;
.FIELD col1 * VARCHAR(2);
.FIELD col2 * VARCHAR(5);

.dml label upsert_into_my_table;
UPDATE my_table  
 SET     
  col1 = :col1,     
  col2 = :col2  
 WHERE col2 = :col2 
INSERT INTO my_table (
col1, col2)  
VALUES (:col1, :col2);

.IMPORT INFILE C:\USER\user\my_tr_file_1.tr
  FORMAT UNFORMAT 
  LAYOUT my_layout APPLY upsert_into_my_table;

.END MLOAD;
.LOGOFF;

Snowflake SQL

OUT -> Teradata_02.sql

EXECUTE IMMEDIATE
$$
  DECLARE
    STATUS_OBJECT OBJECT := OBJECT_CONSTRUCT('SQLCODE', 0);
  BEGIN