Issue in Using CREATE TABLE / TEMP TABLE and WITH Clause Together in BigQuery

Question

I'm encountering a syntax error while attempting to create a temporary table using the CREATE TABLE statement along with a preceding WITH clause in BigQuery.

Error i am getting Syntax error: Unexpected keyword CREATE at [18:1]

Sample reproducible query for reference. Removed all business logic so that it can be run by anyone on their BQ console.

-- Create dummy client products table
WITH 
client_products AS (
  SELECT
    'Retailer A' AS retailer,
    1 AS product_id,
    101 AS group_id,
    '1234567890123' AS gtin,
    'Category A' AS category,
    'Client Product A' AS client_product,
    'Competitor Product A' AS key_competitor_product,
    'Product A' AS product_name,
    'Image A' AS product_image
)

-- All products.
create temp table all_products_test AS (
  SELECT
    retailer,
    product_id,
    group_id,
    gtin,
    category,
    client_product,
    key_competitor_product,
    product_name,
    product_image,
    brand
  FROM client_products
);

I am guessing its because of some scoping issue with CTA in BQ. But i am not sure how to use WITH and TEMP table. I need both for my usecase.

Answer 1

WITH belongs to a SELECT clause, so you need to nest both into the CREATE TABLE clause:

-- Create dummy client products table
create temp table all_products_test AS (

  WITH 
  client_products AS (
    SELECT
      'Retailer A' AS retailer,
      1 AS product_id,
      101 AS group_id,
      '1234567890123' AS gtin,
      'Category A' AS category,
      'Client Product A' AS client_product,
      'Competitor Product A' AS key_competitor_product,
      'Product A' AS product_name,
      'Image A' AS product_image
  )

  -- All products.
  SELECT
    retailer,
    product_id,
    group_id,
    gtin,
    category,
    client_product,
    key_competitor_product,
    product_name,
    product_image,
    brand
  FROM client_products
);