Last updated
Last updated
Some parts in the output code are omitted for clarity reasons.
Adds one or more rows to a table or a view in SQL Server. For more information regarding SQL Server Insert, check .
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 section of this page.
Snowflake
SQL Server
NOTE: As the pattern's name suggests, it is ONLY for cases where the insert comes with a select...from which the body contains a MERGE statement.
1. Syntax elements that require special mappings:
[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
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.
3. The DELETE case is not being considered.
For the INSERT with Table DML Factor with MERGE as DML pattern, the DELETE case is not being considered in the solution, so if the source code merge statement has a DELETE case please consider that it might not work as expected.
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, , and finally, the insert statement.
: Pending Functional Equivalence Review.
: DELETE case is not being considered.
Translation reference for SQL Server Insert statement to Snowflake