I have a workflow where i'm extracting multiple tables for a data source and creating one table in Designer, then outputting that table into an SQL database. This workflow is being run on a daily bases to update the SQL database. However, these tables I am extracting have new columns added to them sometimes which will result in an error from the output tool as the output method is "Delete data & Append".
Is there a way I can add these new columns to the table in the SQL database? Keeping in mind that there is no primary key.
You gonna use an adapted version of the batch input macro, and you gonna pass to the macro the pre sql statement to add the new columns. This is a good post for you to use as reference, it has an example attached:
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.