Select

Translation reference to convert SQL Server Select statement to Snowflake

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

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

IN -> SqlServer_01.sql

SELECT
    MyCol1Alias = COL1,
    MyCol2Alias = COL2,
    COL3 AS MyCol3Alias,
    COL4 MyCol4Alias
FROM TABLE1;

Snowflake

OUT -> SqlServer_01.sql

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

IN -> SqlServer_02.sql

SELECT TOP 1 * from ATable;

Snowflake

OUT -> SqlServer_02.sql

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

IN -> SqlServer_03.sql

SELECT * INTO NEWTABLE FROM TABLE1;

Snowflake

OUT -> SqlServer_03.sql

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

IN -> SqlServer_04.sql

SELECT * INTO NEWTABLE FROM TABLE1
EXCEPT
SELECT * FROM TABLE2
INTERSECT
SELECT * FROM TABLE3;

Snowflake

OUT -> SqlServer_04.sql

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

IN -> SqlServer_05.sql

SELECT TOP 1 PERCENT * from ATable;
SELECT TOP 1 WITH TIES * from ATable;
SELECT TOP 1 PERCENT WITH TIES * from ATable;

Snowflake

OUT -> SqlServer_05.sql

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

IN -> SqlServer_06.sql

SELECT column1, column2 FROM my_table FOR XML PATH('');

Snowflake

OUT -> SqlServer_06.sql

SELECT
--** SSC-FDM-TS0016 - XML COLUMNS IN SNOWFLAKE MIGHT HAVE A DIFFERENT FORMAT **
FOR_XML_UDF(OBJECT_CONSTRUCT('column1', column1, 'column2', column2), '')
FROM
my_table;

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

IN -> SqlServer_07.sql

SELECT column1, column2 FROM my_table OPTION (HASH GROUP, FAST 10);

Snowflake

OUT -> SqlServer_07.sql

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

IN -> SqlServer_08.sql

SELECT AValue from ATable WITH(NOLOCK, NOWAIT);

Snowflake

OUT -> SqlServer_08.sql

SELECT
AValue
from
ATable;

Related EWIs

1. SSC-EWI-0040: Statement Not Supported.

2. SSC-FDM-TS0016: XML columns in Snowflake might have a different format