Remove records from table prior to appending new based on date from text file

dralban

I have a process that receives run_to_date from a text file and creates a large MTD dataset from DW. It then needs to be appended to an existing SAP HANA table, after deleting the old MTD data. I use the run_to_date in Dynamic Input when I build the dataset, can I use it in similar fashion to either execute a generic SQL somehow, or use it in my PreSQL statement in the Output Data?

Basically, I need to delete data from an existing table where year and month equal to the year and month of run_to_date that was input from a text file...

mceleavey

Hi @dralban ,

The easiest way to do this is to load the records into Alteryx, filter out the records you wish to discard, and write the records back to the table, clearing the table first.

This, however, is far from efficient with large datasets, so alternatively you can write the pre-SQL script in the output tool to delete * from table where...

You can wrap that process in a macro, feed the month and Year in as control parameters and overwrite those elements of the Pre-SQL script. This will make those values dynamic.

M.