1

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?

Ravi
  • 11
  • 2
  • if @tab is a variable, then remove @. – Mr.Batra Dec 30 '21 at 05:18
  • Removing the @ didn`t work. I replaced @tab with @tabl1 and still run into the same problem. – Ravi Dec 30 '21 at 05:35
  • So yu mean to say u are now using "@tabl1" intead of '@tab'?. If yes then, it wont work. WHat i meant to say is use just 'tab' or 'tabl1'. – Mr.Batra Dec 30 '21 at 05:39
  • To clarify, I replaced tab with tab1 in both places like here: `"truncate table @tab1" using record.TABLE_NAME as tab1;` – Ravi Dec 30 '21 at 05:48

1 Answers1

1

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);
Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Edward Mitchell
  • 21
  • 3