07 November 2018 · dan-irving ·       Add to Favorites   Report

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 November 2018

Read great educational content like this and a lot more !

Members get free exclusive access to content, new courses, and discounts. Signup for a free account to write a post / comment / upvote posts. Creating an account takes less than 5 seconds and you can start earning badges & points too

Copied