Insert into dataset based on CTE

[supratim320]

Hi Team,

We are working on a particular usecase where a query using "WITH" clause need to be inserted into a dataset which will be used at a later point in time. I have run the "select" clause successfully, however, the "Insert" command is not working in the script. Has anyone tried any such usecase? Curious to know your take on this!

Thank you!

[brekrut]

you should be able to insert into a dataSet but you need to follow the struct syntax of the schema which you are inserting data into.

Please refer to the following page as reference:

https://experienceleague.adobe.com/en/docs/experience-platform/query/sql/syntax#insert-into

what is the error message is your getting?

[supratim320]

Hi @brekrut , Below is a snippet of the query:

The error I am getting says: no viable input 'With...' (it gives the whole query)

I have tried with the "Insert" command at the beginning as well, yielding: combined_result not visible

The resulting dataset (purchase_line_item_test) is correct and I am able to insert records without 'With clause', but in that case query complexity increases

Query:

With combined_result as(
SELECT "Order Number"
, valid_profiles.email_address as "Email"
, "Product Hierarchy Code"
, "Product Name"
, "Quantity"
, "Price"
, "SKU"
FROM purchases)

INSERT INTO purchase_line_item_test (order_number,	email,	product_hier_code,	product_name,	quantity,	price,	sku)
SELECT * FROM combined_result

 Thank you for your response!

[brekrut]

can you provide the schema definition of the dataSet which you are attempting to insert data into?

[supratim320]

Hi @brekrut , this table I am going to use in my accelarated datstore, hence there is no particular dataset/schema I have, rather the table that I have created:

CREATE TABLE purchase_line_item_test AS
SELECT cast(null as string) order_number,
cast(null as string) email,
cast(null as string) product_hier_code ,
cast(null as string) product_name,
cast(0 as integer) quantity,
cast(0.00 as decimal(28,2)) price,
cast(null as string) sku;

[supratim320 · Accepted solution]

I think I did the incorrect way.

Right one:

INSERT INTO purchase_line_item_test

With combined_result AS (
SELECT "Order Number"
, valid_profiles.email_address as "Email"
, "Product Hierarchy Code"
, "Product Name"
, "Quantity"
, "Price"
, "SKU"
FROM purchases)
SELECT "Order Number", "Email, "Product Hierarchy Code", "Product Name", "Quantity", "Price", "SKU"
FROM combined_result;