How to reference variables in Snowflake SQL script

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;
$$

​x
 
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;$$​

Answer

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)

 
select count(*) from identifier(:table_name)​

Advertisement

Answer