Alternative to MERGE for Remote Queries - UPDATE IF EXISTS OR INSERT RECORD

Question

Hi:

I have this query which I use to UPDATE a Record Date when the record ID already exist OR INSERT the Record in the tblMaster. It works fine with the MERGE for local table, but the issue is the table is on REMOTE server and the MERGE statement does not work for remote table (example LINKSERVER.DBName.dbo.tblMaster).

Is there any other alternative? My requirement is to UPDATE the TDate in tblMaster if the records already exists or else INSERT the record.

DROP TABLE IF EXISTS tblMaster   
  
CREATE TABLE tblMaster   
(ID int,  
 TDate DATE)  
  
INSERT INTO  tblMaster values (1, '01/10/2021')  
INSERT INTO  tblMaster values (3, '02/20/2021')  
INSERT INTO  tblMaster values (5, '03/05/2021')  
  
SELECT * FROM tblMaster  
  
DECLARE  @tblIDUpdDates TABLE  
(ID int,  
 UpdDate DATE)  
  
INSERT INTO @tblIDUpdDates values (1,'01/15/2021')  
INSERT INTO @tblIDUpdDates values (2,'02/08/2021')  
INSERT INTO @tblIDUpdDates values (5,'03/08/2021')  
  
  
----UPDATE IF EXISTS OR INSERT New Record  
 MERGE tblMaster WITH (SERIALIZABLE) AS Old  
 USING @tblIDUpdDates AS New  
 ON Old.ID = New.ID  
 WHEN MATCHED  
 THEN  
 UPDATE SET TDate = New.UpdDate  
 WHEN NOT MATCHED  
 THEN  
 INSERT (ID,TDate)  VALUES (New.ID,New.UpdDate);  

SELECT * FROM tblMaster

Answer 1

The alternative is

IF NOT EXISTS (...)
    INSERT
ELSE 
   UPDATE

But beware that updating tables on remote servers often comes with pain and struggle as you have fight both security configuration and performance issues.

Answer 2

Good day,

Using MERGE the target table cannot be remote but the source table can. You can simply execute the query from the server which include the target table using simple MERGE (if this fit)

Answer 3

The function of MERGES is equivalent to combining the update, insert, and delete statements into one statement. If you cannot use merge, you can split the statement into separate update, insert, and delete statements. Please refer to:

 DROP TABLE IF EXISTS tblMaster   
      
 CREATE TABLE tblMaster   
 (ID int,  
  TDate DATE)  
 INSERT INTO  tblMaster values (1, '01/10/2021')  
 INSERT INTO  tblMaster values (3, '02/20/2021')  
 INSERT INTO  tblMaster values (5, '03/05/2021')  
 CREATE TABLE #tblIDUpdDates   
 (ID int,  
  UpdDate DATE)  
 INSERT INTO #tblIDUpdDates values (1,'01/15/2021')  
 INSERT INTO #tblIDUpdDates values (2,'02/08/2021')  
 INSERT INTO #tblIDUpdDates values (5,'03/08/2021')  
   
 ----UPDATE IF EXISTS OR INSERT New Record  
  INSERT INTO tblMaster  
  SELECT ID,UpdDate FROM #tblIDUpdDates  
  WHERE ID NOT IN (SELECT ID FROM tblMaster);  
    
  UPDATE tblMaster  
  SET TDate = #tblIDUpdDates.UpdDate  
  FROM #tblIDUpdDates  
  WHERE tblMaster.ID=#tblIDUpdDates.ID;  
  
  SELECT * FROM tblMaster  

Output:

ID  TDate  
1	2021-01-15  
3	2021-02-20  
5	2021-03-08  
2	2021-02-08  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Looking at this a little closer (and inspired by comment from Ronen which he seems to have deleted later), you can do this with MERGE, but it is more advanced.

You pack the contents of the table variable in an XML document, although the data type must be nvarchar(MAX). Then you build an SQL statement which is to execute on the remote server where you shred that XML into a temp table and then run the MERGE on that temp table. That is, the MERGE statement executes on the remote server. You call the SQL like this:

EXEC SERVER.db.sys.sp_executesql @sql, N'@data nvarchar(MAX)', @data

This does not only have the advantage that you can use MERGE - you are also saved from many of the common problems when you try to update tables on remote servers.

I have some more detailed discussion with some examples (if not with MERGE) in my article on Dynamic SQL: http://www.sommarskog.se/dynamic_sql.html#remotedata.