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.
