Out put SQL table - update, insert if new - with new columns

lydia_yang

Hi Guys,

I am new to Alteryx. I am in Alteryx Admin Design version 2019.4. I have an existing Workflow. It's output is SQL server  TABLE_A_TEMP. It runs every 30 minutes with around 1000 records. There is another table A_TREND with around over 10,000 records. Actually, TABLE_A_TEMP is kind of a temp table, TABLE_A_TREND is the one we used for analytics.

The Post Create SQL Statement is like following. It is the same as update, insert if new to TABLE_A_TREND in my understanding.

delete from TABLE_ A_TREND

where exists

 (select * from TABLE_ A

where TABLE_ A_TREND.my_unique_id = TABLE_ A_TREND. my_unique_id);

INSERT INTO TABLE_ A_TREND

SELECT * FROM TABLE_ A;

The output option is set to Overwrite Table. 

The problem is whenever new calculated columns added in Altrery, new columns in TABLE_A_TEMP added. We have to manually add new columns in TABLE_A_TREND in order to have Post SQL statement run successfully. It is very tedious.

My question is if it is possible to have the output table set to TABLE_A_TREND, then set the option as update, insert if new (I didn't find it in my version.) And is it possible when there is new columns added in Alteryx, the workflow still works without manually add new columns in table design through SQL Editor. 

What is the best practice to simplify the process in my scenario ?

I appreciate your help thank you.

Lydia

cmcclellan

I'd be looking at redesigning the workflow so you don't have to do the Post Create.  That probably means that you have to use TABLE_A_TREND as an input (before the end of the workflow), then join the data so that the result is that you can truncate the entire table (ie Delete Data and Append) and not have any custom SQL at all.  

BUT, if you're adding new fields you will still need to change the structure of the table all the time.  If this is an issue you might have to drop the table and then the Post Create would be to add the GRANTS back on (although a DBA might not like the DROP approach, that's why I usually use the truncate approach)