Insert into dataset based on CTE | Community
Skip to main content
S
supratim320
Level 5
August 13, 2024
Solved

Insert into dataset based on CTE

  • August 13, 2024
  • 1 reply
  • 1452 views

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!

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by 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;

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;

 

1 reply

brekrut
Adobe Employee
brekrutAdobe Employee
Adobe Employee
August 13, 2024

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?

 

    S
    supratim320Author
    Level 5
    August 14, 2024

    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
    Adobe Employee
    brekrutAdobe Employee
    Adobe Employee
    August 14, 2024

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

    Terms & ConditionsAccessibility statement

    Loading footer...