Dynamic SQL DELETE

[Anthony21]

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.

[danilang · Accepted solution]

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 

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

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]

@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