CODEWITHSUNDEEP
sqlgoogle-bigquery
Jas
Jas

Reputation: 834

BigQuery create small sample table all in one query

I know that BigQuery now supports CREATE TABLE ... AS SELECT... like syntax for standard SQL. But does anyone have an example of supplying some sample data to generate this table?

Say I want two columns named A and B. A values should be 1,2,3,4 and B values should be "Alpha", "Bravo", "Charlie", "Delta". How can I generate such a query and create such a table? I realize this would not be efficient for creating large tables, but would like to know the recommended method for creating small tables.

Upvotes: 7

Views: 7910

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

Another, less verbose option is 

CREATE TABLE `PROJECT_ID.DATASET_NAME.NEW_TABLE_NAME` AS  
SELECT * FROM UNNEST([
  STRUCT(1 AS a, 'Alpha' AS b),
  (2, 'Bravo'),
  (3, 'Charlie'),
  (4, 'Delta')
])

or to make it more easily reused with existing csv 

CREATE TABLE `PROJECT_ID.DATASET_NAME.NEW_TABLE_NAME` AS  
SELECT * FROM UNNEST([
  STRUCT(NULL AS a, '' AS b),
  (1, 'Alpha'),
  (2, 'Bravo'),
  (3, 'Charlie'),
  (4, 'Delta')
])
WHERE NOT a IS NULL

Upvotes: 11

Gordon Linoff
Gordon Linoff

Reputation: 1270301

How about select . . . union all?

create table t as
    select 1 as a, 'Alpha' as b union all
    select 2 as a, 'Bravo' as b union all
    select 3 as a, 'Charlie' as b union all
    select 4 as a, 'Delta' as b;

Upvotes: 7

Related Questions