Update Fields in SQL Server Table

[bertal34]

I have reviewed a few different posts on this topic, but I still cannot figure out the proper configuration to successfully work for my case.  Any help is greatly appreciated!!

I’m trying to use either the Output Data or Dynamic Input tool to Update existing records in my SQL table, based on data flowing through my workflow.  Trying to update the table rsch.AG_PROGRAM_PAYMENTS and set the field MATCH_TYPE = MATCH_TYPE from the Alteryx data.  The two sides should link (join) on RecordID.  I read that the SQL table RecordID must be the primary key for this to work, but still having no luck.  Below are the table structures and screenshot of the Output Data tool I’m currently using.

SQL Table “rsch.AG_PROGRAM_PAYMENTS”

RecordID (PK, int, not null)

MATCH_TYPE (varchar(50), null)

Alteryx Workflow

RecordID Int32

MATCH_TYPE V_String 50

Output Data Tool (follows Select (119))

[Treyson · Accepted solution]

Hey there! For clarification, are you just updating that field on keys that already exist or are you also inserting records for keys that don't exist? I'll explain the whole thing and we can remove steps if you are just updating.

What you are looking to do is often called an "upsert". The idea is that we push records to a "temp" or "holding" table on SQL Server. This is not a temp table like we generally think of temp tables, this is just a place to land some data for use in a second process (upserting). Generally, I like to put this in a new schema with the same table name. So if your table is dbo.awesome_table I would create something like hold.awesome_table. This is usually easy because you can grab the DDL from the first table and just change the schema. You write straight to this table from Alteryx using either, "Delete Data and Append" or writing your own truncate script in the pre-SQL section "truncate table hold.awesome_table". I feel like I tested the truncate script to work faster but the farther that goes back in history, the more I think that I was wrong and they work the same.

Then you create your upsert. I like to save them as Stored Procedures because then your code can live in SQL server and Alteryx will just be calling that script. The upsert generally works like this. We compare the keys from both tables. If the keys match and the fields we want to update have different values, we will make some adjustment. If the keys don't match from the landing table, we will insert them. And then sometimes if the keys don't match from the "production" or "target" table, we flag them or remove them. You don't have to do that, but you can. And then you are done!

SQL Server is really good at this function so it really makes sense to have it do this work. The link above has some resources on what that script actually looks like.

[bertal34]

@Treyson thank you very much!!