google-bigquery - How to insert query result into existing table in Google's BigQuery using SQL statement?

Question

I have a huge_query_that_runs_fine_alone that starts with select.

I want to insert that query result into existing table, but usual SQL statements no work.

I tried: insert into test_ds.test_tbl (field1, …, fieldN) values (huge_query_that_runs_fine_alone), but query editor says me that select keyword in unexpected place;

And this: select * into test_ds.test_tbl from (huge_query_that_runs_fine_alone), but query editor says me this Syntax error: Unexpected keyword INTO at [1:10];

What to do?

P.S. Full query...

insert into test_bq_dataset.test_tbl (Naimenovanie_SKU, Naimenovanie_TT, MonthNo, YearNo, AmountPromo, SumPromo, AmountNoPromo, SumNoPromo) values (select promos.Naimenovanie_SKU, promos.Naimenovanie_TT, promos.MonthNo, promos.YearNo, AmountPromo, SumPromo, AmountNoPromo, SumNoPromo from
(select Naimenovanie_SKU, Naimenovanie_TT, MonthNo, YearNo, sum(Prodazhi_Litry) as AmountPromo, sum(Prodazhi_Summa_s_NDS) as SumPromo from IACloud0539_Calc.Data2_PROMO where Promo = false group by Naimenovanie_SKU, Naimenovanie_TT, MonthNo, YearNo) promos
left join
(select Naimenovanie_SKU, Naimenovanie_TT, MonthNo, YearNo, sum(Prodazhi_Litry) as AmountNoPromo, sum(Prodazhi_Summa_s_NDS) as SumNoPromo from IACloud0539_Calc.Data2_PROMO where Promo = true group by Naimenovanie_SKU, Naimenovanie_TT, MonthNo, YearNo) nopromos
on
promos.Naimenovanie_SKU = nopromos.Naimenovanie_SKU
and
promos.Naimenovanie_TT = nopromos.Naimenovanie_TT
and
promos.MonthNo = nopromos.MonthNo
and
promos.YearNo = nopromos.YearNo);

Syntax error: Unexpected keyword SELECT at [1:149]

标签： google-bigquery

Answer 1

You could just place a SELECT query below the INSERT clause and it'll work just fine

ex:

CREATE TABLE temp.mytest5 (col1 STRING, col2 STRING);

And the insertion:

INSERT INTO temp.mytest5 (col1, col2) SELECT 'record1', 'record2'