Using Pre & Post SQL in an Output Tool (via ODBC)

[pjandliz]

I have a workflow I've built which brings in a set of data from Excel which I process for INSERTing into an SQL table.

This data is to replace data already in the output SQL table where records in the table match the date range within the Excel file.

(so, my table contains data for multiple financial years, and I'm trying to replace - say-  the last 3 months of a financial year).

My goal is to delete from the SQL table the old records first, then append the new ones, as this will require less records (and time) to be appended to the table.

From the Excel data, I get the start and end dates for the DELETE, which I use as control parameters to a batch macro.

In the batch macro is an Output Tool to update the SQL table.

I've set up a Pre-SQL statement with 'DELETE FROM table WHERE month BETWEEN 'Start' AND 'End' and use the control parameters to update the Start and End with my dates.

The Output Options for the tool is set to 'Append Existing' (as the input data to the tool are the new records I want to append to the SQL table).

My workflow works fine, except for the fact it does not run the pre-SQL DELETE command. I've tried re-working this multiple ways but no matter what I've done so far, the pre-SQL statement does not run.

I've attached some screenshots of my setup. I can't find much guidance on Pre & Post SQL usage, or it's limitations so I'm hoping someone in the Community can help guide me. Thx.

StartDate and EndDate are string conversions of the Min & Max MonthEnds.

[atcodedog05]

Hi @pjandliz 

Are you getting log message like below in your results window ?

Output Data (54) Executing PreSQL: "DELETE FROM [dbo].[test] Where [id] is not null"

And being successful.

[pjandliz · Accepted solution]

hi @atcodedog05 

The SQL was being executed in a batch macro, so all I would see was '1 iterations were run' (as I passed a single row record with the control parameters to the macro).

Since posting my message I had tried a different approach where I read in all the current records, filtered out the old ones, merged in the new ones and output this to the SQL table. It worked (as it cleared the entire table then appended all records to it) but took almost an hour to run!

I had amended the problematic workflow to run the Pre-SQL as Post-SQL (with the same result) but then changed it back so I could run it and answer your question - and to my surprise it worked correctly! I've no idea what the difference was as I'd set up the control parameters this way before but it seems to be working now (and only taking seconds to run!).

I had shut down & restarted Alteryx since my post so not sure if that had something to do with it.

But now its working I'm ecstatic !!!

Thank you for replying - whilst you didn't do anything it was the catalyst for me to relook and solve it.

[atcodedog05]

Thats great to hear @pjandliz 🙂👍