Import

Translation reference to convert import MLoad to Snowflake SQL

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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

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

3. Layout or DML label not found

Teradata MLoad

IN -> Teradata_03.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 not_layout APPLY insert_into_my_table;

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

.END MLOAD;
.LOGOFF;pl

Snowflake SQL

4. Conditions not found in update statement

Teradata MLoad

IN -> Teradata_04.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
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

Known Issues

Some statements from import clause where not supported yet:

• AXSMOD statement

• INMODE statement

• FORMAT (only FORMAT UNFORMAT is supported)

• WHERE condition of APPLY label

Related EWIS

1. SSC-EWI-0001: Unrecognized token on the line of the source code.

2. SSC-FDM-0027: Removed next statement, not applicable in SnowFlake.