This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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 for your reply. Yes that makes sense. I tried to implement this, but wanted to just optimize and have one single workflow instead of one to update the min and max date in ETLcontrol table and another one to run the actual workflow.
Since the input data is huge, having two workflows to read the same input takes twice the time. Is it possible to read in and write the min and max DATE with file name to ETLcontrol and make the remaining part of the workflow to wait till this happens. Then fetch date from this ETLcontrol table and update accordingly. I thought of using block until done, but not sure if that waits till the date insert happens. Also not sure if I can pass data out of the block until done tool.
The solution here is to use a Batch Macro. The Batch Macro, rather than doing any batching, is just used to make the SQL statement dynamic - this is a fantastic use of batch macro and is often overlooked.
Configure the output module and connect two control parameters with actions to update the start and end dates of the preSQL statement. The macro itself can consist of just two "data" tools, a macro input and an output tool - as well as the 4 interface tools (2 actions and 2 control parameters).
Now in the parent module add the macro (which will have two inputs - the control parameters and the main data feed) and connect the min and max values from a Summarise to the control parameters, and feed the data into the macro input.
Hope that makes sense, I'm on the road today but I can build an example module tonight if needed.
Typically I'd go a step futher and have a log table updated in the post sql script.
In a lot of cases I'd also use a control table to control which tables I load and the changes to the database, but it looks unnecessary in this case. Alteryx makes a great ETL for these situations with the right implementation.