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.

Supported bulk options

SQL Server

Snowflake

FORMAT

TYPE

FIELDTERMINATOR

FIELD_DELIMITER

FIRSTROW

SKIP_HEADER

ROWTERMINATOR

RECORD_DELIMITER

FIELDQUOTE

FIELD_OPTIONALLY_ENCLOSED_BY

Related EWIs

PreviousDropNextCommon Table Expression (CTE)

Last updated