Issue with Combination of INSERT + CTE (with clause) + Dynamic query (IDENTIFIER function)

UdayRPai
New Contributor II

‎09-26-2024 07:30 AM

Hi,

We are trying to insert into a table using a CTE (WITH clause query).
In the insert we are using the INDENTIFIER function as the catalog name is retrieved dynamically.
This is causing the insert to fail with an error - The table or view `cte_query` cannot be found.
Any idea how this cam be fixed?

Example

CREATE TABLE catalog_name.schema_name.target_table1
(
        col1 string,
        col2 date
);
 
DECLARE VARIABLE var_catalog_name STRING;

SET var_catalog_name = (select catalog_name from system.information_schema.catalogs where catalog_name like 'my_catalog%');
 
INSERT INTO IDENTIFIER(var_catalog_name || '.schema_name.target_table1')
(
col1,
col2
)  
WITH cte_query as
(
select  'A' as col1,
        current_date() as col2
)
select  col1,
        col2
from    cte_query
;

 

0 Kudos

UdayRPai
New Contributor II

‎09-26-2024 10:20 AM

Please mark this as resolved.

0 Kudos

szymon_dybczak
Esteemed Contributor III
In response to UdayRPai

‎09-26-2024 10:39 AM

Hi @UdayRPai ,

Could you share solution of you find one? This way you can help community 😉

0 Kudos

UdayRPai
New Contributor II
In response to szymon_dybczak

‎10-03-2024 08:22 AM

Hi,

We used the USE CATALOG statement with EXECUTE IMMEDIATE at the beginning and removed the catalog name from individual insert statements.

 

0 Kudos