Delete before insert in Output Tool

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.

Input

Output

Database Connection

Accepted answers

chris_love

Attached.

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.

Example.yxzp

All comments

s_pichaipillai

Hi Jeeva,

There is no way to parameterize your pre or post SQL in Aleteryx , but yes there are some tricky way to achieve this

What you can do is, create a control table on you data base, something like below and insert the file name what you have if you are dealing with multiple source files

create table ETLcontrol (filename varchar(50),filedate date)

insert ETLcontrol values('Sample',null)

Then configure both post and pre sql with below SQL

--post SQL

Update ETLcontrol

set filedate = (select max(FileDate) from Test)

where filename='Sample'

--Pre SQL

delete Test

where FileDate=(select top 1 filedate from ETLcontrol where filename='Sample')

Pre sql.PNG

So after you configure this and when you run it first time it will load the date into your destination and also POST sql will update the control table for each file with the latest date processed

And when you run it next time PRE SQL will look for the date to be deleted and before you load it will delete the rows. And you need to customize SQL as per your requirement for control table

Hope this may help you to start your SQL with Alteryx

PFA sample for you testing

SQL.yxmd

jeeva_ganesan

Hi. Thanks for your reply. But my file name will be different every month and so I cannot depend on that. My only option here is to use the date column from the incoming data..

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703