CODEWITHSUNDEEP
google-apps-scriptgoogle-bigquery
Daryl Wenman-Bateson
Daryl Wenman-Bateson

Reputation: 3939

App Script BigQuery Standard SQL insert or update statement

I'm looking to use the BigQuery Standard SQL dialect from within Google App Script. The following works in BigQuery when "SQLVersion : Use Legacy SQL" is unchecked;

INSERT INTO MyDataSet.MyFooBarTable (Id, Foo, Date) VALUES (1, 'bar', current_Date)

I can happily interrogate BigQuery from Google Script using Jobs and perform inserts via an Append job (effectively giving the same insert result). Is it possible to use the Standard SQL logic within script to perform the insert?

I have tried adding #standardSQL to the statement with no joy.

Upvotes: 3

Views: 2967

Answers (1)

Graham Polley
Graham Polley

Reputation: 14781

You need to set the useLegacySql flag/parameter to false, to indicate that you want to use standard SQL, like so:

var job = {
configuration: {
  query: {
    query: 'INSERT INTO MyDataSet.MyFooBarTable (Id, Foo, Date) VALUES (1, \'bar\', current_Date);',
    useLegacySql: false
    }

}};

I tested this in my own GAS, and worked as expected.

Upvotes: 6

Related Questions