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
.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;
2. DML label with upsert
.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;
3. Layout or DML label not found
.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
4. Conditions not found in update statement
.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;
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
MSCEWI1002: Removed next statement, not applicable in snowflake.
MSCEWI2088: Layout was not found.
MSCEWI2089: DML label was not found.
MSCEWI2090: Conditions not found in update statement for transformation.
Last updated
Was this helpful?