Can I use the MERGE statement in SQL Server 2005?

Question

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?

Solution

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.