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 (\n  SELECT *\n  FROM `bigquery-public-data.samples.shakespeare`\n  LIMIT 0\n)\nSELECT\n  CONCAT(\n    'INSERT dataset.tablename (',\n    ARRAY_TO_STRING(\n      REGEXP_EXTRACT_ALL(\n        TO_JSON_STRING((SELECT AS STRUCT t.*)),\n        r'"([^"]+)":'),\n      ', '),\n    ')')\nFROM (\n  SELECT AS VALUE t\n  FROM EmptyReference AS t\n  UNION ALL SELECT AS VALUE NULL\n) AS t\n

This returns:

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

dan-irving

posted on