How to reference variables in Snowflake SQL script

Question

I'm writing a script to create 5 tables, but cannot seem to get the SQL DDL part of the statement to work. I get a syntax error " unexpected 'table' ", and I've tried the SQL statement with execute immediate as well. What am I doing wrong?

execute immediate $$ 
declare
  tnames cursor for select value as tname from table( flatten ( ['OPERATIONS.TABLE1','OPERATIONS.TABLE2','OPERATIONS.TABLE3' ] ) );
  src_db_name text default 'SRC_DB';
  tgt_db_name text default 'TARGET_DB';
  dev_schema_name text default 'MYSCHEMA';
begin
    for r in tnames do
        let src_name := src_db_name ||'.'|| r.tname;
        let tgt_name := tgt_db_name ||'.'|| dev_schema_name || '_' || r.tname;
        create table :tgt_name as select * from :src_name ;
        commit;
    end for;
end;
$$

Hello Rav4, please check this KB article on how to use variable in Snowflake: community.snowflake.com/s/article/… — Sheng
– Sheng, Commented Sep 21, 2022 at 5:33

NickW · Accepted Answer · 2022-09-20 20:38:40Z

2

To quote the documentation:

If you are using the variable as the name of an object (e.g. the name of a table in the FROM clause of a SELECT statement), use the IDENTIFIER keyword to indicate that the variable represents an object identifier. For example:

select count(*) from identifier(:table_name)

answered Sep 20, 2022 at 20:38

NickW

10k2 gold badges10 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simeon Pilgrim · Accepted Answer · 2022-09-20 20:46:00Z

If you trust the source data, just build the whole thing as a string:

execute immediate $$ 
declare
  tnames cursor for select value as tname from table( flatten ( ['OPERATIONS.TABLE1','OPERATIONS.TABLE2','OPERATIONS.TABLE3' ] ) );
  src_db_name text default 'SRC_DB';
  tgt_db_name text default 'TARGET_DB';
  dev_schema_name text default 'MYSCHEMA';
  tname text;
  src_name text;
  tgt_name text;
  sql text;
begin
    for r in tnames do
        src_name := src_db_name ||'.'|| r.tname;
        tgt_name := tgt_db_name ||'.'|| dev_schema_name || '_' || r.tname;
        sql := 'create table ' || tgt_name || ' as select * from ' || src_name;
        
        execute immediate :sql;
        commit;
    end for;
    return sql;
end;
$$

Collectives™ on Stack Overflow

How to reference variables in Snowflake SQL script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related