Snowflake Scripting has no direct equivalence with the FORALL statement, however can be emulated with different workarounds to get functional equivalence.
Sample Source Patterns
Setup Data
Oracle
IN -> Oracle_01.sql
CREATETABLEerror_table ( ORA_ERR_NUMBER$ NUMBER, ORA_ERR_MESG$ VARCHAR2(2000), ORA_ERR_ROWID$ ROWID, ORA_ERR_OPTYP$ VARCHAR2(2), ORA_ERR_TAG$ VARCHAR2(2000));--departmentsCREATETABLEparent_table( Id INTPRIMARY KEY,NameVARCHAR2(10));INSERT INTO parent_table VALUES (10, 'IT');INSERT INTO parent_table VALUES (20, 'HR');INSERT INTO parent_table VALUES (30, 'INFRA');--employeesCREATETABLEsource_table( Id INTPRIMARY KEY,NameVARCHAR2(20) NOT NULL, DepartmentID INTREFERENCES parent_table(Id));INSERT INTO source_table VALUES (101, 'Anurag111111111', 10); INSERT INTO source_table VALUES (102, 'Pranaya11111111', 20); INSERT INTO source_table VALUES (103, 'Hina11111111111', 30);--a copy of sourceCREATETABLEtarget_table( Id INTPRIMARY KEY,NameVARCHAR2(10) NOT NULL, DepartmentID INTREFERENCES parent_table(Id));INSERT INTO target_table VALUES (101, 'Anurag', 10);
Snowflake
OUT -> Oracle_01.sql
CREATE OR REPLACETABLEerror_table ( "ORA_ERR_NUMBER$" NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
"ORA_ERR_MESG$"VARCHAR(2000),"ORA_ERR_ROWID$"VARCHAR(18) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWID DATA TYPE CONVERTED TO VARCHAR ***/!!!,"ORA_ERR_OPTYP$"VARCHAR(2),"ORA_ERR_TAG$"VARCHAR(2000))COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;--departmentsCREATE OR REPLACETABLEparent_table ( Id INTPRIMARY KEY,NameVARCHAR(10) )COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;INSERT INTO parent_tableVALUES (10, 'IT');INSERT INTO parent_tableVALUES (20, 'HR');INSERT INTO parent_tableVALUES (30, 'INFRA');--employeesCREATE OR REPLACETABLEsource_table ( Id INTPRIMARY KEY,NameVARCHAR(20) NOT NULL, DepartmentID INTREFERENCES parent_table (Id))COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;INSERT INTO source_tableVALUES (101, 'Anurag111111111', 10);INSERT INTO source_tableVALUES (102, 'Pranaya11111111', 20);INSERT INTO source_tableVALUES (103, 'Hina11111111111', 30);--a copy of sourceCREATE OR REPLACETABLEtarget_table ( Id INTPRIMARY KEY,