CODEWITHSUNDEEP
snowflake-cloud-data-platform
BTag
BTag

Reputation: 37

Snowflake - CREATE table with a date variable in its name

I would like to create a table in snowflake, appending the date to the end of its name. What is the best way to do that?

Tried setting the date variables, but it didn't work.

First attempt:

set var1= (SELECT TO_CHAR(DATE_TRUNC('DAY',CONVERT_TIMEZONE('UTC', CURRENT_DATE())),'YYYYMMDD'));
set var2 = concat('DB.SCHEMA.CLONEME_',$var1);
create table $var2 clone DB.SCHEMA.CLONEME;
-- and got the following error:
-- SQL compilation error: syntax error line 1 at position 13 unexpected '$var2'.

Upvotes: 2

Views: 5683

Answers (1)

Rich Murnane
Rich Murnane

Reputation: 2920

I'd recommend using the IDENTIFIER function:

https://docs.snowflake.com/en/sql-reference/identifier-literal.html

Example:

CREATE OR REPLACE  TABLE CLONEME(
    src_string VARCHAR(20));

INSERT INTO CLONEME 
VALUES('JKNHJYGHTFGRTYGHJ'), ('ABC123'), (null), ('0123456789');

set var1= (SELECT TO_CHAR(DATE_TRUNC('DAY',CONVERT_TIMEZONE('UTC', CURRENT_DATE())),'YYYYMMDD'));
set var2 = concat('CLONEME_',$var1);

SELECT getvariable('VAR1'), getvariable('VAR2');
--20200812  CLONEME_20200812

create table identifier($var2) clone CLONEME;
--Table CLONEME_20200812 successfully created

Upvotes: 6

Related Questions