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:
- 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;