CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquerylegacy-sql
user7463780
user7463780

Reputation:

Big Query - Create a table/view from a temp table

We have a query in Big Query like below

CREATE temp table ttt as (
  SELECT * FROM TABLE
);

EXECUTE IMMEDIATE (
  ---Dynamic query goes here--- 
);

The above query is storing the results in a temporary table as written in the query. How to store these results into an actual table/view so that it can be utilized for further data modelling?

Upvotes: 0

Views: 4584

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

As I already suggested you in your previous question - just add CREATE TABLE your_table AS or INSERT your_table as in below example. Use CREATE (DDL) or INSERT (DML) depends on do you need to create new table or insert into existing one

EXECUTE IMMEDIATE (
  CREATE TABLE dataset.table AS
  SELECT ---Rest of your dynamic query goes here--- 
);

Upvotes: 0

Joe
Joe

Reputation: 314

Try to use:

EXECUTE IMMEDIATE (concat('create table dataset.table as ',---Dynamic query goes here---)
);

Upvotes: 1

Yun Zhang
Yun Zhang

Reputation: 5518

Could you use a persistent table in the first place? Or could you save the results after EXECUTE IMMEDIATE using query below?

CREATE TABLE yourDataset.ttt as (
  SELECT * FROM ttt
);

Upvotes: 0

Related Questions