Perform data dupe check before inserting into SQL Table

cmnflom

I have a flow that runs daily and outputs a daily snapshot of data into a SQL table. I also have a table with historical data that I created using a one-time load in my flow.

What I am trying to do is: every time my flow runs the daily snapshot, I want to compare this daily output with what is already in the historical table, and only insert new data into the historical table.

My unique key consists of the combination of: itemID, storage_location, workweek

What is the best way to do this? I have tried doing a join between the two tables and taking the left side and outputting it to "Update; Insert if New", but I am running into an issue that says "Primary Key Required for Update option" despite specifying the key.

What are some other ways that I can achieve what I am trying to do?

kelsey_kincaid

Hi @cmnflom ,

For the 'Update; Insert if New' feature to work correctly, there has to be a primary key defined in the table itself within SQL. You have a few options if you have no primary key defined:

1. Join & append existing: You're on the right track using a join to help identify which records are new. When you feed the output tool these new records, you can select the "Append existing" option. This leaves your table intact and will union in the new records.

2. Union + Unique & Overwrite Table: You can input your existing table, union in the new data, and use a Unique Tool to dedupe the data. Then you can output to your table and select the "Overwrite Table (drop)" option. This will overwrite your entire table but should have the same end result.