Update SQL table columns

Question

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.

rachelgomez · Answer

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

Felipe_Ribeir0 · Answer

Hi @mahmoodalqadi

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:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamically-pass-Date-in-Output-tool-Pre-SQL-Delete/td-p/449556

mahmoodalqadi · Answer

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?