question

SQL-4608 avatar image
0 Votes"
SQL-4608 asked EchoLiu-msft commented

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

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have any update?
Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
0 Votes"
pituach answered

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)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered pituach edited

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered pituach commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The basic information that the OP should use MERGE from the target server, I already gave.

and I had a "bad" day and I did not want to "fight" and explain that what was provided is not a good solution and repeat myself, so I removed my respond 🤣

0 Votes 0 ·