Still kind of a newbie to SQL Server. Anyway I have two tables: tblStaging and tblWorking. The staging table is updated every 15 minutes and I need to look at what's different in the Staging table and insert the differences into the Working table. I have a combination of two fields that guarantee uniqueness. I also need to go the other way and look at what's in the Working table, that's not in the Staging table and delete those records from the Working table. Sorry for the wordiness. Just wanted to make things clear...
So what is your question?
You can use an UPSERT process - or you can use MERGE, but it really depends on how you want the process to work and how much data is involved.
I needed help because I had no idea even how to start it even. Someone else helped and I did end up using MERGE.
USE [FPY] GO /****** Object: StoredProcedure [dbo].[MergeTables] Script Date: 1/3/2021 6:17:56 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[MergeTables] AS MERGE tblWorking W USING tblStaging S ON W.WONum = S.WONum AND W.OpSeqNum = S.OpSeqNum WHEN NOT MATCHED BY TARGET THEN INSERT (WONum, WOType, OpSeqNum, CellName, BusinessUnit, PartNum, FamilyPart, QtyCompleted, QtyReceived) VALUES(S.WONum, S.WOType, S.OpSeqNum, S.CellName, S.BusinessUnit, S.PartNum, S.FamilyPart, S.QtyCompleted, S.QtyReceived) WHEN MATCHED AND NOT EXISTS (SELECT W.* INTERSECT SELECT S.*) THEN UPDATE SET WONum = S.WONum, WOType = S.WOType, OpSeqNum = S.OpSeqNum, CellName = S.CellName, BusinessUnit = S.BusinessUnit, PartNum = S.PartNum, FamilyPart = S.FamilyPart, QtyCompleted = S.QtyCompleted, QtyReceived = S.QtyReceived WHEN NOT MATCHED BY SOURCE THEN DELETE ;