Update Specific Values in SQL Server

[lmosher]

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!!

[ConnorK]

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.

I hope this helps!

Best,

[lmosher]

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 · Accepted solution]

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,

[lmosher · Accepted solution]

Thanks for your help!