Find column names to insert into BigQuery

Question

I am trying to do the "insert into table" and see that we need to explicitly specify the column names. Is there a way to get this data without having to manual type it out? I am doing this on BigQuery.

Answer 1

Here is an example to get the column names from a table (without incurring a cost) and build the INSERT list at the same time:

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)     

June 2019 Update

Support for omitting column names in INSERT and MERGE statements is now in Beta.

When the column names are omitted, all columns in the target table are included in ascending order based on their ordinal positions

More details here

Answer 2

Using INFORMATION_SCHEMA, Elliott's query can be rewritten as:

WITH ColumnNames AS (
  SELECT column_name FROM dataset.INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = 'tablename'
)
SELECT CONCAT(
  'INSERT dataset.tablename (',
  ARRAY_TO_STRING(ARRAY(SELECT column_name FROM ColumnNames), ', '),    
  ')');