Need to insert differences between Staging and Working tables into the Working Table

1

Hi,
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...

2

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.

3

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
;