on 03-30-201602:50 PM - edited on 07-27-202111:42 PM by APIUserOpsDM
Sometimes you may need records removed from a database table before adding in new records. This can be done using the Pre SQL Statement in the Input tool, which will run an SQL query before, for instance, appending new records to the table. However, what if you do not want to run the same delete statement every time? For example, let's say that you need to delete all records that match a particular date for every run?
This article covers how to delete records from a database using an Input Text tool containing a parameter and a macro using the Post SQL Statement. The macro below is an example of how you can do this.
Create a macro that reads in a date from a Text Input tool from a workflow.
Use the first Action tool to update part of the Post SQL Statement with the date being fed into the macro through the Control Parameter from the workflow. We are updating the ‘x’ as shown below, which is being used as a place holder in the Post SQL Statement, with the date being fed into the macro.
In the Post SQL Statement of the Input tool within the macro, we created the Delete statement with a temporary place holder for the date being read in (‘x’ as shown below). The incoming date will replace the ‘x’ as a result of the Action tool in the previous step.
Use the second Action tool to also update the filter in a smiliar fashion as the input tool:
The filter tool is needed because we are using a post-sql statement for the delete query which means that the tool willfirstread in the data andthenrun the delete, so the records to be deleted will still be read in. The filter is added to stop the deleted records from being part of the macro output.
Important: The reason this is set up that way is because if the delete statement were part of the pre-SQL query, it would be executed as soon as the workflow has been opened which may lead to unintentional deletion of records. The post-SQL statement won't execute until after the table has been read in.
You may also add a macro output for the deleted records to confirm that the correct records were deleted. This will also give you the opportunity to put records back if you didn't mean to delete them as they can be exported from the Alteryx browse window:
Create a workflow that feeds a date from a Text Input tool into the macro.
When the workflow is run, whatever date you have entered in the Text Input tool in the workflow will replace the ‘x’ in the macro, deleting the record(s) that match that date.
Important: If you change the SQL statement, make sure tomake any necessary related changes to the Action tools as well.