Can I fit in Insert query into select statement?-CodePudding

Currently i am working on use case where i required to insert data i have selected but also need to return selected data.So the data selected should be present in result set also should get inserted in one table. Is it possible to do it in single query?I will execute it on BigQuery.

I have checked INSERT INTO SELECT but it will only insert data but won't display selected result.

CodePudding user response：

You can sub-query inside the insert if you need other info just as you can sub-query inside a select, however if you want it to display you'd need a separate select

CodePudding user response：

From https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#insert_statement

You should insert select not insert into select. To give an example:

INSERT dataset.Warehouse (warehouse, state)
SELECT *
FROM UNNEST([('warehouse #1', 'WA'),
      ('warehouse #2', 'CA'),
      ('warehouse #3', 'WA')])

 -------------- ------- 
|  warehouse   | state |
 -------------- ------- 
| warehouse #1 | WA    |
| warehouse #2 | CA    |
| warehouse #3 | WA    |
 -------------- -------

You can also use WITH when using INSERT SELECT. For example, you can rewrite the previous query using WITH:

INSERT dataset.Warehouse (warehouse, state)
WITH w AS (
  SELECT ARRAY<STRUCT<warehouse string, state string>>
      [('warehouse #1', 'WA'),
       ('warehouse #2', 'CA'),
       ('warehouse #3', 'WA')] col
)
SELECT warehouse, state FROM w, UNNEST(w.col)