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:

\n\n
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
\n\n

This returns:

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

dan-irving

posted on

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds




Hi, here we are getting column names is it possible to get values instead of column names.