Bulk Insert

Translation reference for the Bulk Insert statement.

Some parts in the output code are omitted for clarity reasons.

The direct translation for BULK INSERT is the Snowflake COPY INTO statement. The COPY INTO does not use directly the file path to retrieve the values. The file should exist before in a STAGE. Also the options used in the BULK INSERT should be specified in a Snowflake FILE FORMAT that will be consumed by the STAGE or directly by the COPY INTO.

To add a file to some STAGE you should use the PUT command. Notice that the command can be executed only from the SnowSQL CLI. Here is an example of the steps we should do before executing a COPY INTO:

IN -> SqlServer_01.sql

-- Additional Params: -t JavaScript
CREATE PROCEDURE PROCEDURE_SAMPLE
AS

CREATE TABLE #temptable  
 ([col1] varchar(100),  
  [col2] int,  
  [col3] varchar(100))  

BULK INSERT #temptable FROM 'C:\test.txt'  
WITH   
(  
   FIELDTERMINATOR ='\t',  
   ROWTERMINATOR ='\n'
); 

GO

OUT -> SqlServer_01.sql

CREATE OR REPLACE FILE FORMAT FILE_FORMAT_638434968243607970
FIELD_DELIMITER = '\t'
RECORD_DELIMITER = '\n';

CREATE OR REPLACE STAGE STAGE_638434968243607970
FILE_FORMAT = FILE_FORMAT_638434968243607970;

--** SSC-FDM-TS0004 - PUT STATEMENT IS NOT SUPPORTED ON WEB UI. YOU SHOULD EXECUTE THE CODE THROUGH THE SNOWFLAKE CLI **
PUT file://C:\test.txt @STAGE_638434968243607970 AUTO_COMPRESS = FALSE;

CREATE OR REPLACE PROCEDURE PROCEDURE_SAMPLE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
EXECUTE AS CALLER
AS
$$
 // SnowConvert Helpers Code section is omitted.

 EXEC(`CREATE OR REPLACE TEMPORARY TABLE T_temptable
(
   col1 VARCHAR(100),
   col2 INT,
   col3 VARCHAR(100))`);
 EXEC(`COPY INTO T_temptable FROM @STAGE_638434968243607970/test.txt`);
$$

As you see in the code above, SnowConvert identifies all the BULK INSERTS in the code, and for each instance, a new STAGE and FILE FORMAT will be created before the copy into execution. In addition, after the creation of the STAGE, a PUT command will be created as well in order to add the file to the stage.

The names of the generated statements are auto-generated using the current timestamp in seconds, in order to avoid collisions between their usages.

Finally, all the options for the bulk insert are being mapped to file format options if apply. If the option is not supported in Snowflake, it will be commented and a warning will be added. See also SSC-FDM-TS0004.

Supported bulk options

SQL Server	Snowflake
FORMAT	TYPE
FIELDTERMINATOR	FIELD_DELIMITER
FIRSTROW	SKIP_HEADER
ROWTERMINATOR	RECORD_DELIMITER
FIELDQUOTE	FIELD_OPTIONALLY_ENCLOSED_BY

Related EWIs

1. SSC-FDM-TS0004: PUT STATEMENT IS NOT SUPPORTED ON WEB UI.