Snowflake use variable to set Database and Schema

Question

I'm trying to create a variable in Snowflake UI worksheet and use the same in my queries to execute.

Below is a sample query and the error I'm getting. Any workaround to achieve this ?

  SET Database_pre= 'Sample_Pre';  
  SET Schema_pre='ACCOUNT';  
  SET Database_post= 'Sample_Post';   
  SET Schema_post='ACCOUNT';   
  SELECT * FROM   $Database_pre.$Schema_pre."PRODUCT" 
  EXCEPT 
  SELECT * FROM   $Database_post.$Schema_post."PRODUCT"

This is the error I'm getting while running the above query Error - SQL compilation error: syntax error line 1 at position 16 unexpected '$Database_pre'. syntax error line 3 at position 16 unexpected '$Database_post'.

user_13658399 · Answer

This is covered in the documentation: https://docs.snowflake.com/en/sql-reference/session-variables.html#using-variables-in-sql

You need to use IDENTIFIER() or TABLE() round your variables

user_11227919 · Answer

try this , it should work.

create table PRODUCT (fld1 varchar2(100), fld2 varchar2(100));
create table PRODUCT_COPY (fld1 varchar2(100), fld2 varchar2(100));

SET Database_pre = 'Database1';  
SET Schema_pre='SCH'; 
SET TGT_TABLE1='PRODUCT'

SET Database_post= 'Database1';   
SET Schema_post='SCH'; 
SET TGT_TABLE2='PRODUCT_COPY'

set t=$Database_pre||'.'||$Schema_pre||'.'||$TGT_TABLE1;
set t1=$Database_post||'.'||$Schema_post||'.'||$TGT_TABLE2;
 
 
 select * from identifier($t)
 EXCEPT
  select * from identifier($t1);

Snowflake use variable to set Database and Schema

2 Answers