Insert into TABLE($TABLE_VARIABLE) snowflake

Question

I am using snowflake I am looking to insert data to a table while using a variable The purpose of using the variable is so when I can change it without doing a find and replace all

CREATE OR REPLACE TABLE DB1.PUBLIC.HUMANS (
  HUMAN VARCHAR(32)
)
;

The following works

INSERT INTO DB1.PUBLIC.HUMANS
SELECT 'SUCESS';

The following does not work

SET EXPORT_TABLE = 'DB1.PUBLIC.HUMANS';

INSERT INTO TABLE($EXPORT_TABLE)
SELECT 'FAILURE';

HOWEVER this works.

SELECT * FROM TABLE($EXPORT_TABLE);

Is there is a way to insert into a table defined by a table literal?

reference documentation:

https://docs.snowflake.com/en/sql-reference/literals-table.html https://docs.snowflake.com/en/sql-reference/sql/insert.html

====================================================== Update: Answer found by comments below. Thanks to Biraja Mohanty and Greg Pavlik

To make this work have to wrap the IDENTIFIER().

INSERT INTO IDENTIFIER($EXPORT_TABLE);

https://docs.snowflake.com/en/sql-reference/session-variables.html

To add to what Biraja Mohanty shared, the reason table() worked for select but not for insert is because TABLE() is a function that returns a table. If you pass as its only parameter the name of a table, the TABLE function will return the rows in that table. — Greg Pavlik

Biraja Mohanty Biraja Mohanty · Accepted Answer · 2020-10-29T17:28:23

2

votes

SET EXPORT_TABLE = 'DB1.PUBLIC.HUMANS';

INSERT INTO identifier($EXPORT_TABLE) SELECT 'FAILURE';

Insert into TABLE($TABLE_VARIABLE) snowflake

1 Answers