Bulk Insert

Translation reference for the Bulk Insert statement.

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:

source.sql

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

target.sql

-- STEP 1: Create a FILE FORMAT
CREATE OR REPLACE FILE FORMAT FILE_FORMAT_637692072327877197
FIELD_DELIMITER = '\t'
RECORD_DELIMITER = '\n';

-- STEP 2: Create a STAGE
CREATE OR REPLACE STAGE STAGE_637692072327877197
FILE_FORMAT = FILE_FORMAT_637692072327877197;

-- STEP 3: Put the file into the stage
/*** MSC-INFORMATION - MSCINF0023 - PUT STATEMENT IS NOT SUPPORTED ON WEB UI. YOU SHOULD EXECUTE THE CODE THROUGH THE SNOWFLAKE CLI ***/
PUT file://C:\test.txt @STAGE_637692072327877197 AUTO_COMPRESS = FALSE;

CREATE OR REPLACE PROCEDURE BULK_PROC ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   // REGION SnowConvert Helpers Code
   // END REGION

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

-- ** MSC-WARNING - MSCEWI1040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE **
--GO

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 MSCEWI4023.

Supported bulk options

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