Dynamic Date for a delete query SQL

Question

Hi

I am trying to read a file from a folder with data to be loaded into an sql table, previously i would use the post sql to remove duplicates but we need to improve this - so I thought

read contents of new file get min and max date from a column, 
then build a delete query using these dates - can do this 
then pass the delete query to sql to run the delete - cant do this

I have attached an image of how i was trying to do it using chats but have got nowhere, i have tried a dynamic input and even tried a macro but cant get my head round how to do this - and it seems so simple

any thoughts links on how to do this - with a delete from query

have looked at a few videos and doesnt seem like anyone has done like this

alteryx delete image.png

OllieClarke · Answer

Hey @rob2003 ,

If you use a batch macro, you can pass your delete command in through the control parameter.

Here I've got a simple batch macro with a pre-sql statement in the output tool

Using the action tool, I can select this pre-sql statement and replace it with one constructed in my workflow

It would be basically identical with a post-sql statement.

Hope that helps,

Ollie

rob2003 · Answer

I hadn't thought of that way might be an idea but the table is large and a lot of columns - will dig a bit further into this

jrlindem · Answer

I'm not sure on the answer to your question of passing a field value to the Post-SQL.  I believe this isn't possible, but others can correct me.

What I've done to solve this, though, is bring the data in from that table, union it to your new data.  Use the Min() and Max() dates from your new data feed and filter out all the old data that you don't want to keep, then load all of that back into the same table...

Or you could use "Update: Insert if New" if your table has a set primary key.  This essentially uses the primary key to help you only load records that are new, while retaining or updating any that exist.  If that's not what you're after, then I would go back to my first suggestion.

Hope that helps get you started if considering a different direction is viable, -Jay