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.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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.
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)
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.
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.