Insert

Translation reference for SQL Server Insert statement to Snowflake

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

INSERT INTO TABLE1 VALUES (1, 2, 123, 'LiteralValue');

Snowflake

INSERT INTO PUBLIC.TABLE1 VALUES (1, 2, 123, 'LiteralValue');

INSERT with assing operator

SQL Server

INSERT INTO aTable (columnA = 'varcharValue', columnB = 1);

Snowflake

INSERT INTO PUBLIC.aTable (columnA = 'varcharValue', columnB = 1);

INSERT with no INTO

SQL Server

INSERT exampleTable VALUES ('Hello', 23);

Snowflake

INSERT
PUBLIC.exampleTable VALUES ('Hello', 23);

INSERT with common table expression

SQL Server

WITH ctevalues (textCol, numCol) AS (SELECT 'cte string', 155)
INSERT INTO exampleTable SELECT * FROM ctevalues;

Snowflake

/*** MSC-WARNING - MSCEWI4020 - COMMON TABLE EXPRESSION IS NOT SUPPORTED FOR INSERT STATEMENT. TABLE(RESULT_SCAN(LAST_QUERY_ID())) WAS USED INSTEAD IN ORDER TO PRESERVE THE CURRENT BEHAVOUR ***/
SELECT 'cte string', 155;
INSERT INTO PUBLIC.exampleTable SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) AS ctevalues;

Known Issues

1. Syntax elements that require special mappings:

• [WITH common_table_expression]: Generates a temporal table alias to be used in the insert. Requires a transformation that involves RESULT_SCAN(), check MSCEWI4020 for more information.

• [INTO]: This keyword is obligatory in Snowflake and should be added if not present.

• [DEFAULT VALUES]: Inserts the default value in all columns specified in the insert. Should be transformed to VALUES (DEFAULT, DEFAULT, ...), the amount of DEFAULTs added equals the number of columns the insert will modify. For now, there is a warning being added.

  SQL Server

  INSERT INTO exampleTable DEFAULT VALUES;

  Snowflake

  -- ** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR INSERT WITH DEFAULT VALUES IS PLANNED TO BE DELIVERED IN THE FUTURE **
  --INSERT INTO PUBLIC.exampleTable DEFAULT VALUES

2. Syntax elements not supported or irrelevant:

• [TOP (expression) [PERCENT]]: Indicates the amount or percent of rows that will be inserted. Not supported.

• [rowset_function_limited]: It is either OPENQUERY() or OPENROWSET(), used to read data from remote servers. Not supported.

• [WITH table_hint_limited]: These are used to get reading/writing locks on tables. Not relevant in Snowflake.

• [<OUTPUT Clause>]: Specifies a table or result set in which the inserted rows will also be inserted. Not supported.

• [execute_statement]: Can be used to run a query to get data from. Not supported.

• [dml_table_source]: A temporary result set generated by the OUTPUT clause of another DML statement. Not supported.

Related EWIs

1. MSCEWI4020: COMMON TABLE EXPRESSION IS NOT SUPPORTED FOR INSERT STATEMENT

2. MSCEWI1037: TRANSLATION FOR INSERT WITH DEFAULT VALUES IS PLANNED TO BE DELIVERED IN THE FUTURE