CREATE TABLE AS SELECT and INSERT
Saving a query result as a table lets other queries read it again. A saved table sometimes needs rows that the query could not produce.
How it works
CREATE TABLE ... AS SELECT runs a query once and stores the result as a new table. INSERT then adds rows to a table that already exists.
CREATE TABLE name AS SELECT ...takes the column names and types from theSELECT, so give every output column a name withAS. The new table has no partitioning or clustering, whatever the source tables have.CREATE TABLE name (column TYPE, ...)makes an empty table with the columns you list.INSERT INTO name (columns) SELECT ...then fills it, and the query columns go to the listed columns by position.INSERT INTO name (columns) VALUES (...), (...)adds the rows you write out. A column that is not in the list gets its default value. A column made by aCREATE TABLE ... AS SELECThas no default.- A saved table is a copy of the result at that moment. It does not change when its source tables change. An
INSERTalways adds rows and never replaces a row that has the same key.
Common mistake
An inner join drops every row that has no partner on the other side. A customer who never ordered is missing from the saved totals unless you add that row yourself.
Worked example
CREATE TEMP TABLE budget_products AS
SELECT product_id, product_name
FROM `learn.shop.products`
WHERE price < 10;
INSERT INTO budget_products (product_id, product_name) VALUES (0, 'Gift card');
SELECT product_id, product_name FROM budget_products ORDER BY product_id;The first statement saves the products that cost less than 10. The INSERT adds a row that no product could have produced.
The last query reads the finished table. TEMP makes a table that lasts only as long as the script, and the products table is not changed.