Update SQL table columns

[mahmoodalqadi]

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.

[Felipe_Ribeir0]

Hi @mahmoodalqadi 

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.

[mahmoodalqadi]

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? 

[Felipe_Ribeir0]

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-P...

[rachelgomez]

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