
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.
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:
- The new table is populated with the returned data
- 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:
- A
select
query - 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