CREATE TABLE AS SELECT in Snowflake

You can create a table in Snowflake manually with a table definition, or you can do so directly from a select query, which can save you a lot of time. In this post I will explain how you can do this, and cover common questions that users have.

What is CREATE TABLE AS SELECT?

CREATE TABLE AS SELECT creates a new table that is structured and filled with the data returned by a select query.

The benefit of using this method to create a table is two fold:

1. The new table is populated with the returned data
2. You don’t need to define each column’s name, datatype and constraint

The use case for this approach is being able to get a pre-loaded dataset into the hands of end users, or as a way to improve data load times.

Create table using SELECT query in SQL

The SQL command create table as select will create a new table filled with the data from a select query. In it’s simplest form, this command requires just two arguments:

1. A select query
2. The table name it will create

After these are defined, the table definition and data loading will happen automatically behind the scenes.

Example of using CREATE TABLE AS SELECT in Snowflake

This method is very similar regardless of the database technology you are using, but let’s look an example of implementing it in Snowflake:

create table <table_name> as <select_query>;

create table products_on_sale as 
select product_id, price from products
where on_sale = True;

Create a SQL table from a view

You can use create table as select with an already defined view instead of a new select query. This is possible as a view is essentially a saved SQL query which can be called upon later.

Let’s look at an example of implementing this in Snowflake. We will create a new view and then use this newly defined view in create table as select :

create view products_on_sale_view as 
select product_id, price from products
where on_sale = True;

create table products_on_sale as 
select * from products_on_sale_view;

Using CREATE TABLE AS SELECT with added constraints

You can develop further upon the create table as select statement by adding constrains to both the table and the columns. This is recommended as a way to improve code quality and reduce the chance of errors occurring.

CREATE OR REPLACE TABLE AS SELECT

If you want to create a table but it already exists, an error will be returned. This can be avoided by using the create or replace command, which will replace the table if it already exists.

create or replace table <table_name> as <select_query>;

create or replace table products_on_sale as 
select product_id, price from products
where on_sale = True;

CREATE TABLE AS SELECT with column constraints

Just because you are creating the table and populating it with a select query, doesn’t mean that you can’t add column constraints. In this example we will build on our previous product_on_sale query by adding some of the most common constraints.

create or replace table products_on_sale (
product_id numeric not null,
price numeric)
as 
select product_id, price from products
where on_sale = True;

Related articles

Tables

How to create a temporary table in Snowflake
Create table as select in Snowflake

Views

View vs table in SQL
Materialized view vs table in SQL

Functions

Select all columns except one or more

References

Snowflake documentation