Comparing SQL Table to Updated File - Then Update / Insert

[tonyppham]

Hi There,

I'm a heavy SQL user and have been using SSIS to push data to our SQL Staging tables. The Staging tables are compared to the Production tables to see what records have been updated, deleted, or brand new. I use the SQL MERGE statement to do this. Can someone explain to me a high-level proof-of-concept workflow of achieving this within Alteryx? I'm still very new to Alteryx and trying to understand its capabilities.

Thanks!!

[vishwa_0308]

You can compare tables using Join tool. I have illustrated the basic way to do it.

You can create flags also to check which are the fields get updated.

[tonyppham]

Hi Viswha, 

Thanks for your quick response. Basing off your screenshot, you have 3 outputs. Can you clarify what the appropriate next steps would be?

1 (L): Instead of deleting these, I'd like to retain them. I assume I would not have to do anything. 

2 (J): I thought these records would exist in both tables? I'd like to update the existing record in the Production table with data from the new table. Is the Write In-DB the appropriate one to use? 

3(R): I would like to have these appended to the existing Production table. Union-in-DB tool the appropriate one to use? 

Thank you!

[jrgo]

Hi @tonyppham,

Possible that I misunderstood something, but would the option below not perform the action you're looking for? This is from a standard "Output Data" tool, not the "Write In-DB". The Update options do rely that your table contain PK's.

Hope this helps!

Best,

Jimmy

[tonyppham]

Hi jrgo,

I believe that should accomplish what I'm trying to achieve. I'm building out a proof-of-concept right now to verify! I'll keep you posted ;) 

[vishwa_0308]

Hi @tonyppham,

1. Correct, just keep the deleted items in new table and keep it updated using union tool every time when you get deleted results.

2. For existing records in both the tables keep all the fields from the new tables unless you dont want to keep track of the previous data. you can save the records using Output tool also.

Regards,

Vishwa