Automate backing up a table, deleting all rows and writing new info in order

[ghbronner]

Hello,

I am truly struggling doing something I thought it would be rather simple in Alteryx, most likely because I am fairly new to the tool. 

I have a simple table in my db called trigger that I need to backup (append) to another table called trigger_backup, then delete all its records and then populate it with new data from a local file I have. All of this needs to happen in the same workflow.

Things I am struggling with:

-I don't know how to make Alteryx run the backup first, delete all records second, and write new info third. I need things to happen in this order.

-I don't know how to get Alteryx to delete all rows in a table. I've seen this: https://community.alteryx.com/t5/Data-Sources/Delete-database-records/td-p/39929, but the answer did not make sense to me or maybe I need a more step by step explanation.

-I don't know how to get Alteryx to append data to the trigger_backup table. It seems that when it writes back to a table, it tries to replace all its values (I am testing the output with .csv file).

In summary, this is what I have:

Trigger Table:

Column A	Column B
A_2017	High
B_2017	High
C_2017	Low

Trigger_backup Table:

Column A	Column B
A_2016	Low
B_2016	Low

New Data File:

Column A	Column B
A_2018	Low
B_2018	High
C_2018	High

And this is what I need:

Trigger Table:

Column A	Column B
A_2018	Low
B_2018	High
C_2018	High

Trigger_backup Table:

Column A	Column B
A_2016	Low
B_2016	Low
A_2017	High
B_2017	High
C_2017	Low

I'd appreciate any help that can come from this question.

Best,

ghbronner

[fevizcaino · Accepted solution]

Hello ghbronner,

Look the attached example and see if it fits your needs.

Related to replacing all database values, look the attached image where you can select an option for substituting all database values.

[ghbronner]

Thank you!

It does exactly what I needed, nice trick with the Sample Tool.

Best,

ghbronner

[PeterGoldey]

Hi,

This is a process I actually do all the time.  Another approach would be as follows:

Trigger backup

1. use Input Data to read in from your Trigger table
2. use Output Data to write into your Trigger backup table using "Append Existing" as the output option
   1. You didn't mention anything about unique values.  If you have a unique constraint on the backup table, depending on your DB type, you will need to have a PRIMARY KEY defined (not just a unique index) and then use "Update - insert if new" as the output option

You can use the block until done setup that fevizcaino provided to tie the parts together

New data insert

1. use Input Data to read in your csv
2. use Output Data to write into your Trigger table
   1. Output Options you can use "Delete Data and Append" if you want to keep your column definitions in place and indexes
   2. OR you can use "Overwrite table (drop)" but that will recreate the table based on the datatypes / lengths that Alteryx finds in the new CSV file and you would have to put in Post Create SQL Statement any index create statements

If I missed anything, let me know, but this should be very straight forward using just this handful of tools. 

[ghbronner]

Thanks for the reply! What you mentioned makes perfect sense.

I do have unique constraint, however it is not a problem for the data I am using (I add a time stamp to the trigger data I append to the backup table).

Best,

ghbronner