What are SELECT INTO Alternatives in Snowflake?

Post author:Vithal S
Post last modified:August 29, 2022
Post category:Snowflake
Reading time:5 mins read

In my other post, I have discussed about what are different methods to create Snowflake table. There are many database specific syntaxes that are not supported in Snowflake yet. One of such syntax is SELECT INTO. The databases such as SQL Server, Reshift, Teradata, etc. supports SELECT INTO clause to create new table and insert the resulting rows from the query into it. In this article, we will check what are SELECT INTO alternatives in Snowflake with some examples.

SELECT INTO Alternatives in Snowflake

In the databases such as SQL Server and Teradata, there are two types of SELECT INTO statements.

Create New Table using SELECT INTO table
SET Variable using SELECT INTO variable

SELECT INTO table Alternatives in Snowflake

The SELECT…INTO creates a new table and inserts the resulting rows from the query into it. The format of new_table is determined by evaluating the expressions in the select list. The SELECT INTO clause comes in handy when you have a requirement to create table from a query. For example, create a temporary table to hold result of the query.

Following statement uses SELECT INTO to create a new table new_table in Amazon Redshift.

SELECT 
	ID,
	name,
	age
INTO new_table
FROM TestTable;

The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list.

CREATE TABLE AS (CTAS) As SELECT INTO Alternatives in Snowflake

CREATE TABLE AS (CTAS) is another common method to creates a new table based on a query. This method creates a new table populated with the data returned by a query.

The following statement can create table in Snowflake using CTAS.

CREATE OR REPLACE TABLE new_table
AS
SELECT 	ID,
	NAME,
	AGE
FROM TestTable;

In conclusion, the CTAS method creates a new table and insert records just like SELECT INTO.

SELECT INTO variable Alternatives in Snowflake

The databases such as Teradata PLSQL uses SELECT INTO clause to set variable inside stored procedures.

For example

select mim(DATE_COL) into MIN_DT from MY_TABLE;

Where MIN_DT is a variable.

The alternative approach for above query is to use a subquery to set variable.

For example,

CREATE OR REPLACE TABLE MY_TABLE (DATE_COL DATE) AS SELECT $1 FROM VALUES ('2018-01-01'),('2018-03-01');
 
SET MIN_DT = (SELECT MIN(DATE_COL) FROM MY_TABLE);
 
SELECT $MIN_DT;