Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Delete before insert in Output Tool

jeeva_ganesan
8 - Asteroid

Hi All,

 

I am trying to output data from Alteryx output tool to SQL Server database. I would like to delete the old records(which I am trying to insert) for that particular period from database before insert.  

Input data will have the date column. Is it possible to access that field values from "Pre Create SQL Statement" in Output tool?
 
In brief, my requirement is that, every month I get data for past few months(in csv format).  For example, first time I get data for Jan 2013 - Jan 2015 and load it in to database. Next time I will get data for Feb 2013- Feb 2015. But this time while loading in to database, I want to keep what is there in database for Jan 2013, delete data for Feb 2013 - Feb 2015 and load new data for Feb 2013 - Feb 2015 from csv. I can get these date period using max(DATE) and min(DATE) from input csv. But how to reference that in my DELETE statement in Pre Create SQL Statement. ?
 
Thanks in advance.
14 REPLIES 14
clant
8 - Asteroid

Thank you Chris! I spent ages trying to do this, I used your solution and got it working in no time!

kinan
6 - Meteoroid

Thank you so much, Chris. Your macro example was clear and I managed to adapt it to my needs. Been trying to find solutions for three days now. Can't thank you enough!

anil_m
8 - Asteroid

@chris_love

 

Your solution is helpful,  is it possible to pass dynamic arguments to 'pre create sql statement' instead of hard coding.

chris_love
12 - Quasar
Yes it is. To achieve this you put the tool into a batch macro as then use
the parent macro to build the dynanic sql and pass it through as a control
parameter.
lanningb
7 - Meteor

Hello.

When I try to do this in my App, the Batch Macro tries to process(write) the Input data multiple times. For example, if the data I send in has 20 rows, it will try to add those 20 rows 20 times! Is there a configuration I need to have to make it only process my input data 1 time?

Labels