Creating a snapshot of a table in AJO

[Richard_at_Qantas]

Hi, I need to create a snapshot of my profile snapshot dataset in AJO so I can access values stored in aggregate fields in PowerBI. I have tried scheduling a query to select the columns and counters I need and write them to a dataset. The problem I have is that the scheduled query either adds rows to the table (thus I get duplicates), using the "Use Existing Dataset" option or schedule never runs when I use the "Use New Dataset". I've tried adding the SQL DELETE FROM tablename at the start of the query only to have my query fail with delete from tool_extract_layers_and_locations; 6:52:08 PM > Running query... 6:52:46 PM > Query failed in 38.285 seconds. 6:52:46 PM > ErrorCode: 42601 Syntax error encountered. Reason: [Invalid command!] How can I run a scheduled query (eg run every second day) , create a dataset with a fixed name, and only have it contain the results of the last time the query executed?

[AtulChavan · Accepted solution]

Hi @Richard_at_Qantas ,
Not sure on your requirement with AJO but with regards to creating table with same name here are your options - 

In AEP query service you may use drop table first and later create statement.DROP TABLE [IF EXISTS] [db_name.]table_name

You can also go with drop and create view. Go through this to learn about syntax and options AEP supports : https://experienceleague.adobe.com/docs/experience-platform/query/sql/syntax.html?lang=en#select-que...