CODEWITHSUNDEEP
loopsgoogle-bigqueryexecute-immediate
Ravi
Ravi

Reputation: 11

BigQuery Execute Immediate Identifier issue

I am creating a BQ Stored Procedure to truncate all the tables in a dataset. I have a 2 step process. Step 1 identifies all the matching tables. Step 2 is expected to iterate thru each table and truncate.

I have the following code to achieve this:

for record in
 ( select TABLE_NAME 
  from <staging_dataset>.INFORMATION_SCHEMA.TABLES
  )
DO
  execute immediate 
  "truncate table @tab" using record.TABLE_NAME as tab;
END FOR;

The Error that I am running into is in the Execute Immediate piece.

Invalid EXECUTE IMMEDIATE sql string `truncate table @tab`, Syntax error: Unexpected "@" at [8:3]

I tried replacing the @tab with ? placeholder and see a similar error. What am I doing wrong? Is there another way to achieve the same result?

Upvotes: 1

Views: 1658

Answers (1)

Edward Mitchell
Edward Mitchell

Reputation: 21

Strange; it seems DML doesn't work with USING. Queries work fine.

Try using CONCAT to build your dynamic query string:

CONCAT("truncate table ", record.TABLE_NAME);

Upvotes: 1

Related Questions