Insert

Translation reference for SQL Server Insert statement to Snowflake

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

Description

Adds one or more rows to a table or a view in SQL Server. For more information regarding SQL Server Insert, check here.

Syntax comparison

The basic insert grammar is equivalent between both SQL languages. However there are still some other syntax elements in SQL Server that show differences, for example, one allows the developer to add a value to a column by using the assign operator. The syntax mentioned will be transformed to the basic insert syntax too.

For information about other special syntax elements in SQL Server like the ones shown in the below example please refer to the Known Issues section of this page.

Snowflake

INSERT [ OVERWRITE ] INTO <target_table> [ ( <target_col_name> [ , ... ] ) ]
       {
         VALUES ( { <value> | DEFAULT | NULL } [ , ... ] ) [ , ( ... ) ]  |
         <query>
       }

SQL Server

[ WITH <common_table_expression> [ ,...n ] ]  
INSERT   
{  
        [ TOP ( expression ) [ PERCENT ] ]   
        [ INTO ]   
        { <object> | rowset_function_limited   
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]  
        }  
    {  
        [ ( column_list ) ]   
        [ <OUTPUT Clause> ]  
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ]   
        | derived_table   
        | execute_statement  
        | <dml_table_source>  
        | DEFAULT VALUES   
        }  
    }  
}  
[;]  
  
<object> ::=  
{   
    [ server_name . database_name . schema_name .   
      | database_name .[ schema_name ] .   
      | schema_name .   
    ]  
  table_or_view_name  
}  
  
<dml_table_source> ::=  
    SELECT <select_list>  
    FROM ( <dml_statement_with_output_clause> )   
      [AS] table_alias [ ( column_alias [ ,...n ] ) ]  
    [ WHERE <search_condition> ]  
        [ OPTION ( <query_hint> [ ,...n ] ) ] 

Sample Source Patterns

Basic INSERT

SQL Server

IN -> SqlServer_01.sql
INSERT INTO TABLE1 VALUES (1, 2, 123, 'LiteralValue');

Snowflake

OUT -> SqlServer_01.sql
INSERT INTO TABLE1 VALUES (1, 2, 123, 'LiteralValue');

INSERT with assing operator

SQL Server

IN -> SqlServer_02.sql
INSERT INTO aTable (columnA = 'varcharValue', columnB = 1);

Snowflake

OUT -> SqlServer_02.sql
INSERT INTO aTable (columnA = 'varcharValue', columnB = 1);

INSERT with no INTO

SQL Server

IN -> SqlServer_03.sql
INSERT exampleTable VALUES ('Hello', 23);

Snowflake

OUT -> SqlServer_03.sql
INSERT INTO exampleTable VALUES ('Hello', 23);

INSERT with common table expression

SQL Server

IN -> SqlServer_04.sql
WITH ctevalues (textCol, numCol) AS (SELECT 'cte string', 155)
INSERT INTO exampleTable SELECT * FROM ctevalues;

Snowflake

OUT -> SqlServer_04.sqll
INSERT INTO exampleTable
WITH ctevalues (
textCol,
numCol
) AS (SELECT 'cte string', 155)
SELECT
*
FROM
ctevalues AS ctevalues;

INSERT with Table DML Factor with MERGE as DML

This case is so specific where the INSERT statement has a SELECT query, and the FROM clause of the SELECT mentioned contains a MERGE DML statement. Looking for an equivalent in Snowflake, the next statements are created: a temporary table, the merge statement converted, and finally, the insert statement.

SQL Server

IN -> SqlServer_05.sql
INSERT INTO T3
SELECT
  col1,
  col2
FROM (
  MERGE T1 USING T2
  	ON T1.col1 = T2.col1
  WHEN NOT MATCHED THEN
    INSERT VALUES ( T2.col1, T2.col2 )
  WHEN MATCHED THEN
    UPDATE SET T1.col2 = t2.col2
  OUTPUT
  	$action ACTION_OUT,
    T2.col1,
    T2.col2
) AS MERGE_OUT
 WHERE ACTION_OUT='UPDATE';

Snowflake

OUT-> SqlServer_05.sql
--** SSC-FDM-TS0026 - DELETE CASE IS NOT BEING CONSIDERED, PLEASE CHECK IF THE ORIGINAL MERGE PERFORMS IT **
CREATE OR REPLACE TEMPORARY TABLE MERGE_OUT AS
SELECT
	CASE WHEN T1.$1 IS NULL THEN 'INSERT' ELSE 'UPDATE' END ACTION_OUT,
	T2.col1,
	T2.col2
FROM T2 LEFT JOIN T1 ON T1.col1 = T2.col1;

MERGE INTO T1
USING T2
ON T1.col1 = T2.col1
WHEN NOT MATCHED THEN INSERT VALUES (T2.col1, T2.col2)
WHEN MATCHED THEN UPDATE SET T1.col2 = t2.col2
!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - OUTPUT CLAUSE NOT SUPPORTED IN SNOWFLAKE ***/!!!
OUTPUT
	$action ACTION_OUT,
	T2.col1,
	T2.col2 ;

INSERT INTO T3
SELECT col1, col2