community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

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

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 AColumn B
A_2017High

B_2017

High

C_2017

Low

 

Trigger_backup Table:

Column AColumn B
A_2016Low
B_2016Low

 

New Data File:

Column AColumn B
A_2018

Low

B_2018

High

C_2018

High

 

And this is what I need:

 

Trigger Table:

Column AColumn B
A_2018Low

B_2018

High

C_2018

High

 

Trigger_backup Table:

Column AColumn B
A_2016Low
B_2016Low
A_2017

High

B_2017High
C_2017Low

 

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

 

Best,

 

ghbronner

Alteryx Partner

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.

 

 

Thank you!

 

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

 

Best,

 

ghbronner

 

Alteryx Partner

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. 

 

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 

Labels