How to reference variables in Snowflake SQL script-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

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