Select
Translation reference to convert SQL Server Select statement to Snowflake
Description
Allows the selection of one or more rows or columns of one or more tables in SQL Server.
For more information regarding SQL Server Select, check here.
<SELECT statement> ::=
[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]
<query_expression>
[ ORDER BY <order_by_expression> ]
[ <FOR Clause>]
[ OPTION ( <query_hint> [ ,...n ] ) ]
<query_expression> ::=
{ <query_specification> | ( <query_expression> ) }
[ { UNION [ ALL ] | EXCEPT | INTERSECT }
<query_specification> | ( <query_expression> ) [...n ] ]
<query_specification> ::=
SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
< select_list >
[ INTO new_table ]
[ FROM { <table_source> } [ ,...n ] ]
[ WHERE <search_condition> ]
[ <GROUP BY> ]
[ HAVING < search_condition > ]
Sample Source Patterns
SELECT WITH COLUMN ALIASES
The following example demonstrates how to use column aliases in Snowflake. The first two columns, from the SQL Server code, are expected to be transformed from an assignment form into a normalized form using the AS
keyword. The third and fourth columns are using valid Snowflake formats.
SQL Server
SELECT
MyCol1Alias = COL1,
MyCol2Alias = COL2,
COL3 AS MyCol3Alias,
COL4 MyCol4Alias
FROM TABLE1;
Snowflake
SELECT
COL1 AS MyCol1Alias,
COL2 AS MyCol2Alias,
COL3 AS MyCol3Alias,
COL4 MyCol4Alias
FROM
TABLE1;
SELECT TOP
The basic case of SQL Server Select Top is supported by Snowflake. However, three more cases exist that are not supported, you can check them in the Known Issues section.
SQL Server
SELECT TOP 1 * from ATable;
Snowflake
SELECT TOP 1
*
from
ATable;
SELECT INTO
The following example shows the SELECT INTO
is transformed into a CREATE TABLE AS
, this is because in Snowflake there is no equivalent for SELECT INTO
and to create a table based on a query has to be with the CREATE TABLE AS
.
SQL Server
SELECT * INTO NEWTABLE FROM TABLE1;
Snowflake
CREATE OR REPLACE TABLE NEWTABLE AS
SELECT
*
FROM
TABLE1;
Another case is when including set operators such as EXCEPT
and INTERSECT
. The transformation is basically the same as the previous one.
SQL Server
SELECT * INTO NEWTABLE FROM TABLE1
EXCEPT
SELECT * FROM TABLE2
INTERSECT
SELECT * FROM TABLE3;
Snowflake
CREATE OR REPLACE TABLE NEWTABLE AS
SELECT
*
FROM
TABLE1
EXCEPT
SELECT
*
FROM
TABLE2
INTERSECT
SELECT
*
FROM
TABLE3;
Known Issues
SELECT TOP Aditional Arguments
Since PERCENT
and WITH TIES
keywords affect the result, and they are not supported by Snowflake, they will be commented out and added as an error.
SQL Server
SELECT TOP 1 PERCENT * from ATable;
SELECT TOP 1 WITH TIES * from ATable;
SELECT TOP 1 PERCENT WITH TIES * from ATable;
Snowflake
SELECT
TOP 1 !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
*
from
ATable;
SELECT
TOP 1 !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
*
from
ATable;
SELECT
TOP 1 !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
*
from
ATable;
SELECT FOR
Since the FOR
clause is not supported in Snowflake, it is commented out and added as an error during the transformation.
SQL Server
SELECT column1, column2 FROM my_table FOR XML PATH('');
Snowflake
SELECT
column1,
column2
FROM
my_table
!!!RESOLVE EWI!!! /*** SSC-EWI-TS0044 - FOR XML CLAUSE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
FOR XML PATH('');
SELECT OPTION
The OPTION
clause is not supported by Snowflake. It will be commented out and added as a warning during the transformation.
Notice that the OPTION
statement has been removed from transformation because it is not relevant or not needed in Snowflake.
SQL Server
SELECT column1, column2 FROM my_table OPTION (HASH GROUP, FAST 10);
Snowflake
SELECT
column1,
column2
FROM
my_table;
SELECT WITH
The WITH
clause is not supported by Snowflake. It will be commented out and added as a warning during the transformation.
Notice that the WITH(NOLOCK, NOWAIT)
statement has been removed from transformation because it is not relevant or not needed in Snowflake.
SQL Server
SELECT AValue from ATable WITH(NOLOCK, NOWAIT);
Snowflake
SELECT
AValue
from
ATable;
Related EWIs
SSC-EWI-0040: Statement Not Supported.
SSC-EWI-TS0044: FOR XML clause is not supported in Snowflake.
Last updated