MERGE: updating two tables, one check? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MERGE: updating two tables, one check?

Author

Topic

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-28 : 14:03:42

I have new data in a Temp table, existing data in a Main table, and an Archive table which holds all current and historical data from Main.
1.The Main table is updated from Temp table if existing record changed, else new records are inserted.
2.The Archive table is updated from Main table if existing record changed, else new records are inserted.
The exact same data is updated into both tables, so why do I need to check twice?
Is there any way in which the following code can be refined, to check once, then update both Main and Archive at the same time?

Update Main Table Code:


MERGE
INTO	PlayerSkillsShort USING 
		(SELECT		Players.PlayersID, UpdatedSquad.Form, UpdatedSquad.Fatigue, UpdatedSquad.Date
                FROM	UpdatedSquad INNER JOIN Players ON UpdatedSquad.PlayerIDCode = Players.PlayerIDCode) AS Upd
                ON PlayerSkillsShort.PlayersID = Upd.PlayersID
WHEN MATCHED
		AND (Upd.Form <> PlayerSkillsShort.Form OR
                     Upd.Fatigue <> PlayerSkillsShort.Fatigue)
THEN
UPDATE    
SET		PlayerSkillsShort.Form = Upd.Form, PlayerSkillsShort.Fatigue = Upd.Fatigue, PlayerSkillsShort.date = Upd.date
WHEN NOT MATCHED
		THEN
INSERT	(PlayersID, Form, Fatigue, Date)
VALUES  (PlayersID, Form, Fatigue, Date);

Update Archive Table Code:


INSERT INTO ArchivePlayerSkillsShort
                      (ArchivePlayersID, PlayerSkillsShortID, PlayersID, Form, Fatigue, Date)
SELECT     ArchivePlayers.ArchivePlayersID, PlayerSkillsShort.PlayerSkillsShortID, PlayerSkillsShort.PlayersID, PlayerSkillsShort.Form, 
                      PlayerSkillsShort.Fatigue, PlayerSkillsShort.Date
FROM         PlayerSkillsShort INNER JOIN
                      ArchivePlayers ON PlayerSkillsShort.PlayersID = ArchivePlayers.PlayersID LEFT OUTER JOIN
                      ArchivePlayerSkillsShort AS ArchivePlayerSkillsShort_1 ON PlayerSkillsShort.PlayerSkillsShortID = ArchivePlayerSkillsShort_1.PlayerSkillsShortID AND 
                      PlayerSkillsShort.Date = ArchivePlayerSkillsShort_1.Date
WHERE     (ArchivePlayerSkillsShort_1.PlayerSkillsShortID IS NULL)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-28 : 14:31:57

You will have to have two MERGE.
An UPDATE statement can only update one table at a time.

E 12°55'05.63"
N 56°04'39.26"

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-06-28 : 17:02:08

ok thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources