Generate Insert statement with column names in BigQuery

While doing an insert, you will have to specify the column names in BigQuery. Its a pain to generate the column list every time manually. 

Here is a quick hack to get the column names from a table (without incurring a cost) and build the INSERT list automatically:

WITH EmptyReference AS (
  SELECT *
  FROM `bigquery-public-data.samples.shakespeare`
  LIMIT 0
)
SELECT
  CONCAT(
    'INSERT dataset.tablename (',
    ARRAY_TO_STRING(
      REGEXP_EXTRACT_ALL(
        TO_JSON_STRING((SELECT AS STRUCT t.*)),
        r'"([^"]+)":'),
      ', '),
    ')')
FROM (
  SELECT AS VALUE t
  FROM EmptyReference AS t
  UNION ALL SELECT AS VALUE NULL
) AS t

This returns:

INSERT dataset.tablename (word, word_count, corpus, corpus_date)    

dan-irving

posted on 07 Nov 18