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