Insert to different Datasets using Query Service

This is unfortunately not possible, since the INSERT INTO and CTAS tables are single dataset only.

Hi ​@Michael_Soprano 

No, it is not possible to insert data into multiple datasets using a single SQL query in AEP Query Service. The SQL syntax supported by Query Service allows for only one target table per operation.

Thanks,

Santosh Kumar

Hi ​@Michael_Soprano,

Not sure if this will work but give it a try may be using an anonymous block with the below format :
Ref : Anonymous Block in Query Service | Adobe Experience Platform
 

$$

BEGIN

  INSERT INTO abc

  SELECT ...

  FROM source_dataset;

/*Note the below dataset will be profile disabled by default you can update the flag to profile enable it

CREATE TABLE table_name
  [WITH (schema='target_schema_title', rowvalidation='false', label='PROFILE')]

*/

  CREATE TABLE xyz(your dataset name)

  WITH (schema = 'your_xdm_schema')

  AS

  SELECT *

  FROM abc;

END

$$;

In that case these are executed in sequence and the first dataset gets populated using the query result while the 2nd one will just copy the data into the secondary dataset if that is the ask. 

But ideally only one insert into dataset operation is allowed per transaction.

Regards,
Sayantan