CODEWITHSUNDEEP
sqlsql-serverinsertmerge
Charles Clayton
Charles Clayton

Reputation: 17946

How to update both the source and the target using MERGE?

I have two tables that I want to merge. Each has row contains the last time it was modified. I want to merge the tables such that the newest value is assigned to both tables.

This is the code I want to use, what's commented out is what's causing my problems.

-- synchronize databases

MERGE [ClientDB].[dbo].[Table] trgt
USING [MasterDB].[dbo].[Table] src

ON trgt.ID = src.ID

WHEN MATCHED THEN
  -- IF src.LastModified > trgt.LastModified   -- if the source is newer 
       UPDATE SET trgt.[Info] = src.[Info]     -- update the target
  -- ELSE 
    -- UPDATE SET src.[Info] = trgt.[Info]     -- otherwise update the source

WHEN NOT MATCHED BY SOURCE
    THEN DELETE

WHEN NOT MATCHED BY TARGET
    THEN INSERT ([Info]) VALUES (src.[Info]);

It doesn't work for two reasons,

1) it doesn't appear to syntactically like the IF statement nested inside the WHEN MATCHED THEN.

2) It also doesn't want me to update the source, which is problematic because I want to synchronize both ways. If I switch trgt and src I get the error:

The multi-part identifier "src.Description" could not be bound.

How can I accomplish this? Should I abandon MERGE or does it have the capability to do what I want?

I'm extremely new to SQL so feel free to correct my thinking if it's wrong anywhere. Thanks in advance.

Upvotes: 2

Views: 1952

Answers (3)

Charles Clayton
Charles Clayton

Reputation: 17946

I just needed to call merge twice. I decided that only the master database could add or remove entire rows.

This worked for me:

-- merge databases 

-- update the client from the master 
MERGE [ClientDB].[dbo].[table] trgt
using [MasterDB].[dbo].[table] src
ON trgt.id = src.id 

WHEN matched AND trgt.lastmodified <= src.lastmodified THEN -- if the master has a row newer than the client                      
  UPDATE SET trgt.[info] = src.[info], ...                  -- update the client 

WHEN NOT matched BY source -- delete any rows added by a client 
THEN 
  DELETE 
WHEN NOT matched BY target -- insert any rows added by the master 
THEN 
  INSERT ( [info], ... ) VALUES (src.[info], ... ); 

-- update the master from the client 
MERGE [MasterDB].[dbo].[table] trgt 
using [ClientDB].[dbo].[table] src 
ON trgt.id = src.id 

WHEN matched AND trgt.lastmodified < src.lastmodified THEN  -- if the client is newer than the master               
  UPDATE SET trgt.[info] = src.[info], ...          -- update the master

Upvotes: 2

Dijkgraaf
Dijkgraaf

Reputation: 11527

As stated in the other answers and comments, you can't do a two way merge in one statement and will have to use more than one MERGE statement. In fact you would require four

  1. To update ClientDB (including the LastModified comparison in the ON statement)
  2. To update MasterDB (including the LastModified comparison in the ON statement)
  3. To insert ClientDB where the record doesn't exist
  4. To insert MasterDB where the record doesn't exist

Which rather defeats the use of the MERGE statement and you could use standard UPDATE & INSERT queries.

Note: You also probably don't want the WHEN NOT MATCHED BY SOURCE THEN DELETE as that would defeat getting new records from the other database.

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40309

The long and short is that SQL Server only lets you modify (insert, update, or delete) one table per statement. Updating two table will require two statements.

Upvotes: 1

Related Questions