BigQuery create small sample table all in one query

Question

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.

Answer 1

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