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
\n\nThis returns:
\n\nINSERT dataset.tablename (word, word_count, corpus, corpus_date) \n
dan-irving
posted onEnjoy 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
Post Comment