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

Question

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

tim8w

Old Hand

Points: 375
More actions
January 3, 2021 at 6:14 am

#3826683

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246970 More actions · Answer 1

What sort of 'updates' do you need to look for? INSERTS, UPDATES and/or DELETES?

Do your tables have DateCreated and DateModified columns? If they do, this exercise probably becomes easier and faster than it will be otherwise.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 2

INSERT INTO tblWorking
(
    Col1,
    Col2,
    Col3,
    ...
)
SELECT Col1,
       Col2,
       Col3,
       ...
  FROM tblStaging s
 WHERE NOT EXISTS(SELECT *
                    FROM tblWorking w
                   WHERE w.Col1 = s.Col1
                     AND w.Col2 = s.Col2)

DELETE w
  FROM tblWorking w
 WHERE NOT EXISTS(SELECT *
                    FROM tblStaging s
                   WHERE s.Col1 = w.Col1
                     AND s.Col2 = w.Col2)

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

tim8w wrote:

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

"It Depends"... how many columns do you have in your staging and target tables?

If it's more than just a small handful, I've found that having a precalculated MD5 hash in the target table and quickly building one in the staging table works wonders for eliminating rows that exist in both very quickly, which makes checking for which rows in the staging table need to be inserted and those that need updating very fast... especially in the presence of a common key column (or columns).

And, yes... calculating the hashes in the staging table and comparing those to the target table hashes is a whole lot faster than comparing every column in a join every times especially if you have more than a small handful of columns in the staging table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tim8w Old Hand Points: 375 More actions · Answer 4

Hi,

Thanks for all the help. I ended up doing this after I received some suggestions:

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 tblJDEWOData 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
;