12

I am using SQL Server 2005 and I wanted to create MERGE statement or concept in single query in SQL Server 2005. Is it possible?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vikrant More
  • 5,182
  • 23
  • 58
  • 90

1 Answers1

27

MERGE was introduced in SQL Server 2008. If you want to use that syntax, you'll need to upgrade.

Otherwise, the typical approach will depend on where the source data is from. If it's just one row and you don't know if you need to update or insert, you'd probably do:

UPDATE ... WHERE key = @key;

IF @@ROWCOUNT = 0
BEGIN
    INSERT ...
END

If your source is a #temp table, table variable, TVP or other table, you can do:

UPDATE dest SET ...
  FROM dbo.destination AS dest
  INNER JOIN dbo.source AS src
  ON dest.key = src.key;

INSERT dbo.destination SELECT ... FROM dbo.source AS src
  WHERE NOT EXISTS (SELECT 1 FROM dbo.destination WHERE key = src.key);

As with MERGE (and as Michael Swart demonstrated here), you will still want to surround any of these methods with proper transactions, error handling and isolation level to behave like a true, single operation. Even a single MERGE statement does not protect you from concurrency.

I've published some other cautions about MERGE in more detail here and here.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 1
    Of course, for some exotic setups, it may not be possible to replace a `MERGE` with separate `INSERT`/`UPDATE`/`DELETE` statements. – Damien_The_Unbeliever Sep 27 '12 at 12:36
  • @Damien_The_Unbeliever maybe true, but as far as I know, `MERGE` hasn't solved any problems that weren't solvable before, it just might have been a lot more complex. There are some common misconceptions out there about how `MERGE` can prevent key violations or how it will only fire DML triggers once. And in fact there are some cases where `MERGE` can't be used as a simple swap-in replacement. So it's certainly not a panacea, or even well understood even after 4+ years. – Aaron Bertrand Sep 27 '12 at 12:43
  • 1
    @AaronBertrand - [One example I can think of](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id) – Martin Smith Sep 27 '12 at 12:44
  • @MartinSmith [another one](http://michaeljswart.com/2011/09/mythbusting-concurrent-updateinsert-solutions/) – Aaron Bertrand Sep 27 '12 at 12:45
  • The specific scenario I've used it in is a sort of "double-linked-list" structure built using foreign keys. (Not that relationally pure, but hey, it works for its specific purpose). When you want to add a new row, you have to `INSERT` it and `UPDATE` two existing rows to reference it. You could achieve it with separate statements, if SQL Server supported deferred constraint checking, but it doesn't. – Damien_The_Unbeliever Sep 27 '12 at 12:55
  • @Damien why do you think MERGE is safer or better? You still have to escalate isolation level etc. to achieve the same thing in a protected manner in both cases. MERGE is syntactic sugar but it doesn't blend the operations into a single automic operation unless you make it so. – Aaron Bertrand Sep 27 '12 at 12:57
  • can't we do something like in we code it in the DLL and used in the project like this. can we create Merge statement or i mean can we did changes in the Engine of SQL SERVER 2005 where we provide this features and used in coding. – Vikrant More Sep 27 '12 at 14:08
  • 4
    @VikrantMore no, you cannot make changes to the database engine. The way to do that would be called upgrading. – Aaron Bertrand Sep 27 '12 at 14:08
  • see i mean like "select" is the keyword used in the sql can we create such a keywords or add in sql server 2005 ? – Vikrant More Sep 27 '12 at 14:11
  • 1
    @VikrantMore no, you can't create your own keywords in any version of SQL Server, sorry. Perhaps you are mistaking SQL Server for one of the open source database platforms? – Aaron Bertrand Sep 27 '12 at 14:15
  • 2
    @VikrantMore: You can do this kind of things (add functions, clauses, statements) in open-source DBMSs, like Postgres and MySQL. Not in SQL-Server or Oracle. – ypercubeᵀᴹ Sep 27 '12 at 14:15
  • @Aaron Bertrand i don't want to say that way. i mean can we do this by creating any SP and writing an generalize code in it. – Vikrant More Sep 27 '12 at 14:20
  • 1
    @VikrantMore I suppose it's possible you could write a generic procedure that mimics merge (e.g. using the approaches above, or similar), but unless it was only for a single table, it would have to use dynamic SQL and all kinds of conditional logic, and I don't know what that would gain you. – Aaron Bertrand Sep 27 '12 at 14:22
  • @AaronBertrand's "other cautions" link is WELL worth reading. And scary! – Mike K Feb 17 '15 at 20:00