topic Re: Issue with Combination of INSERT + CTE (with clause) + Dynamic query (IDENTIFIER function) in Data Engineering

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

UdayRPai — Thu, 26 Sep 2024 14:30:55 GMT

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

;

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

UdayRPai — Thu, 26 Sep 2024 17:20:27 GMT

Please mark this as resolved.

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

szymon_dybczak — Thu, 26 Sep 2024 17:39:55 GMT

Hi @UdayRPai ,

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

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

UdayRPai — Thu, 03 Oct 2024 15:22:04 GMT

Hi,

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