Automate backing up a table, deleting all rows and writing new info in order
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you!
It does exactly what I needed, nice trick with the Sample Tool.
Best,
ghbronner
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
This is a process I actually do all the time. Another approach would be as follows:
Trigger backup
- use Input Data to read in from your Trigger table
- use Output Data to write into your Trigger backup table using "Append Existing" as the output option
- 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
- use Input Data to read in your csv
- use Output Data to write into your Trigger table
- Output Options you can use "Delete Data and Append" if you want to keep your column definitions in place and indexes
- 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
