Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Update Specific Values in SQL Server

lmosher
6 - Meteoroid

Hi, 

 

I've got a table in SQL Server that has a primary key that I've built using an Alteryx workflow that pulls hourly reports from a share drive. Right now it's set up so that it will only append new records that aren't currently in the table. How can I modify the workflow such that it will identify duplicate values, delete the existing ones in the SQL Server table, and insert the most recent records from the new hourly report? 

 

Any advice would be greatly appreciated!

 

Thanks!!

4 REPLIES 4
ConnorK
Alteryx
Alteryx

Hi @lmosher ,

 

In order to do this you will want to use the output option "Update:Insert If New" within your Output tool. I find that this process works best for me when using and OleDb connection to SQL.

 

ConnorK_0-1573831282341.png

 

 

I hope this helps!

 

 

Best,

Connor Kelleher
Senior Sales Engineer
Alteryx
lmosher
6 - Meteoroid

Hi Connor, 

 

Thanks for the quick response! 

 

I saw that option, but I wasn't quite certain about its functionality, so perhaps you can help clarify; "Update, Insert if New," will effectively delete the existing records (based off of the duplicates coming in from the newest hourly report) in the table, and then replace it with the new record? 

 

I thought that maybe it would just insert the new record if one of the fields were different; this is part of the reason why I created the primary key. I never got around to testing it to see if that would solve my problem. 

ConnorK
Alteryx
Alteryx

Hi @lmosher ,

 

Yes, if you choose to use "Update: Insert If New" it will look at the primary keys of the records you currently have in your database and verify if anything has changed. If something has changed it will update that record. Also if you have new records with new primary keys, then these records will be added to the database at the same time.

 

 

Best,

Connor Kelleher
Senior Sales Engineer
Alteryx
lmosher
6 - Meteoroid

Thanks for your help!

Labels