Hi,
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.
1)You could use the option overwrite table, this would drop your table and create a new one with all columns (more easy to do)
2)Or you could use the batch input macro and and pass a dynamic pre sql statement creating the new columns.
Thank you @Felipe_Ribeir0 for your response.
1) I am trying to avoid overwriting the table as it will effect the performance on the DB.
2) I don't quite understand how to approach this, do you have an example you can share?
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.
Regards,
Rachel Gomez