I have a table in the database that I need to update based on the records being passed to it. I am given a table-valued-parameter that has the same schema as the existing table (for all intensive purposes). The requirements are:

  • If the TVP (source) contains a record with existing ID in the existing table (target) update that record, if it is not marked as deleted in the existing table and its modified date is more recent.

    • Conditions: source.Id = target.Id AND target.IsDeleted = 0 AND source.Modified > target.Modified
  • If the TVP contains a record for which the ID does not exist in the existing table, insert the record to the table.

I have the following set up so far.

EDITED: This setup works to give the desired end result. I, however, don't believe this is the best way of getting the intended result. There are two select statements that calculate the source table for the Merge statement. I would like to avoid having to filter out the source table in this manner.

My question is this: is there a way to achieve the same result as the query below but without having to use two select statements as is currently used in the Merge statement?

IF OBJECT_ID('tempdb..#target') IS NOT NULL DROP TABLE #target    
IF OBJECT_ID('tempdb..#source') IS NOT NULL DROP TABLE #source

CREATE TABLE #target  ( Id int, Modified int, IsDeleted bit, NoteText varchar(500) )    
INSERT #target VALUES (1, 1, 0, '') 
INSERT #target VALUES (2, 1, 1, '') 
INSERT #target VALUES (3, 3, 0, '') 

CREATE TABLE #source ( Id int, Modified int, NoteText varchar(500) )     
INSERT #source VALUES (1, 2, 'Modified') 
INSERT #source VALUES (2, 2, 'Modified')
INSERT #source VALUES (3, 2, 'Modified')
INSERT #source VALUES (4, 2, 'Modified') 

merge #target as t 
using (select * from #source s where s.id not in (select id from #target where IsDeleted = 1 OR Modified > s.Modified)) as source
on t.id=source.id 
when matched then update set Modified=source.Modified, NoteText = source.NoteText 
when not matched then insert values (id, Modified, 0 ,NoteText) ;

SELECT * FROM #target; 

-- Expected records in #target after operations
-- 1,2,0,'Modified'  ** UPDATED **         
-- 2,1,1,''          ** NOT UPDATED BECAUSE ISDELETED = 1 **
-- 4,2,0,'Modified'  ** INSERTED **

drop table #target;
drop table #source
  • 1
    Why do you think you need to use MERGE here? Please read mssqltips.com/sqlservertip/3074/… and the comments... Jan 21, 2015 at 5:04
  • 1
    Hi Aaron, I have read and made reference to that article in one of my other posts (dba.stackexchange.com/questions/89506/…) I am aware of those issues and will be taking pointers from there in the final version of the above query. For now, I am just focusing on getting the most optimal solution. Oh, fantastic article, BTW :)
    – GMalla
    Jan 21, 2015 at 5:08
  • 1
    Why do you think MERGE will be more optimal than the old-fashioned way we did it with update and insert? MERGE might look like one statement but it's not one operation. Jan 21, 2015 at 5:10
  • I am not sure if MERGE is better or not. I want to compare it against a different query I have. Using XML parameter, and Update-Insert-and-IF-NOT-EXISTS, it took me about 2 minutes to update 1000 records. I just want to see if merge can do any better.
    – GMalla
    Jan 21, 2015 at 5:13
  • 1
    I haven't had a chance to compare my MERGE with the standard update and insert. I want to be able to do that next. To that, I want to get the correct MERGE query. You are correct in rewording the question. When I originally asked the question, I did not have any solution at all. I edited the query with one of the solutions I found after posting the question which changed the question's tone. So, you are correct, now the question is: Is this MERGE statement the most efficient way to write this MERGE statement?
    – GMalla
    Jan 21, 2015 at 14:47

1 Answer 1


A simpler way to write the MERGE is as follows:

USING #source AS S
    ON S.Id = T.Id
    AND T.IsDeleted = 0
    AND S.Modified > T.Modified
        Modified = S.Modified,
        NoteText = S.NoteText
    INSERT (Id, Modified, IsDeleted, NoteText)
    VALUES (S.Id, S.Modified, 0, S.NoteText);

The SERIALIZABLE hint would be necessary to avoid a potential race condition. This consideration obviously does not apply to a local temporary table target, but it would be remiss of me not to mention.

In broad terms, it is almost always wrong to perform any sort of filtering in the ON clause (or equivalently, in a CTE). The ON clause should only be used to specify how rows between the source and target are related.

See Inserting, Updating, and Deleting Data by Using Merge for more details.

The source and target tables should also have a unique index or key. Moreover, for best performance SQL Server should be able to guarantee that the MERGE statement will affect each target row a maximum of once. Without that, the execution plan will have to detect multiple DML operations on the same row in order to raise a runtime error if it happens. This often requires a Sort, and always requires a Segment, Sequence Project, and Assert. None of these will make your command execute any faster :)

  • This is exactly what I was looking for. On WITH SERIALIZABLE, would replacing with HOLDLOCK hint work the same way as mentioned here: "UPSERT" Race Condition with MERGE? Thanks again!
    – GMalla
    Jan 22, 2015 at 14:06
  • Did not know that. Is there any literature that you could recommend me which I can reference to understand this more in detail? Any article/ publication or even a book would do. Thanks again!
    – GMalla
    Jan 22, 2015 at 14:13
  • 3
    @predestination If you're asking about concurrency and locking, try Kalen's book (the PDF version is free).
    – Paul White
    Jan 22, 2015 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.