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.

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.

All BigQuery lessons