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

Dynamic SQL DELETE

Anthony21
6 - Meteoroid


I am looking to create a piece of SQL that will dynamically delete rows of metrics from a SQL table so that they can be updated with fresh data. Many workflows are feeding different metrics into the same table so this workflow needs to delete prior versions (where they exist) and load new versions. I had previously achieved something similar with a Bulk SQL load tool in a Batch Macro. I was able to use macro inputs to alter pre-SQL and post-SQL statements.

 

The macro runs something like this :

 

Batch Macro:

- CONTROL PARAMETER = Metric 1

PreSQL DELETE FROM Metrics WHERE MetricID='CONTROL_PARAMETER'

BulkSQLLoader Metric 1

PostSQL INSERT INTO Metrics_ETL SELECT 'CONTROL_PARAMETER', GETDATE()

- Next CONTROL_PARAMETER

- Repeat

 

In this case the batch macro ran once per metric but the performance of this macro became slow with increasing data. So I am looking for an approach to bulk delete metrics. ie

 

PreSQL DELETE FROM Metrics WHERE MetricID IN 'Metric120','Metric121;'Metric122'....)

BulkSQLLoader 'Metric120','Metric121;'Metric122'....

 

I've tried a number of approaches but it has become insanely complicated. Suggested approaches welcome as I have lost my way. I have found the learning material around the Dynamic Input tools thin and none appear to support modifying DELETE FROM SQL statements, they appear to assume SELECT. For example the error I get trying to get the Dynamic Input tool to run DELETE FROM is:

 

Error: Dynamic Input (51): Error opening "SELECT * FROM DELETE From Metrics Where MetricID = 'Metric1' OR MetricID = 'Metric2' OR...

 

It is also frustrating that macros will not accept inputs into Action tools from anything but inputs from control parameters.

2 REPLIES 2
danilang
19 - Altair
19 - Altair

Hi @Anthony21 

 

You should be able to continue using a macro if you modify your technique like this.

 

Within the macro, have a dynamic input with your pre Sql configured like this 

 

i.png

 

Connect the control parameter to your input and configure the Action tool like so

 

di.png

 

In your main workflow, use a Summarize to build up the values for the IN clause ('Metric120','Metric121','Metric122') and pass these to the control parameter. 

 

This way you should be able to batch your deletes and avoid the slow batch iteration problem

 

Dan

 

 

Anthony21
6 - Meteoroid

@Danilag. That works, I had lost my way trying to get something neater. I have implemented a solution now with the data load within a Batch macro of one iteration, no grouping. That macro actually exists within a standard macro that creates the SQL string for the Pre-SQL statement.

 

Its a curious property of the Batch macro that you can create a control parameter from a dataflow, that isn't true of Standard Macros. It's ironic that the solution to the problem I saw was in my original starting point.

 

Thanks for your help.

Anthony

Labels