Can I use the MERGE statement in SQL Server 2005?

by

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.

More Related Contents:

  1. How to parse string into date?
  2. Is it possible to write single query on two table which are not connected to each other?
  3. SQl CASE Statement
  4. How to update two tables in one statement in SQL Server 2005?
  5. SQL query to select dates between two dates
  6. Create a date from day month and year with T-SQL
  7. How to get Time from DateTime format in SQL?
  8. Selecting COUNT(*) with DISTINCT
  9. How much size “Null” value takes in SQL Server
  10. SQL – Subtracting a depleting value from rows
  11. What is a “batch”, and why is GO used?
  12. T-SQL loop over query results
  13. SELECT FOR UPDATE with SQL Server
  14. Easiest way to populate a temp table with dates between and including 2 date parameters
  15. How to insert default values in SQL table?
  16. How to Round a Time in T-SQL
  17. WHERE clause on SQL Server “Text” data type
  18. SQL Server error on update command – “A severe error occurred on the current command”
  19. Return a value if no rows are found in Microsoft tSQL
  20. Check if a varchar is a number (TSQL)
  21. Calculating timespan with t-sql
  22. Using Aliases in Where Clause or an Alternative Option?
  23. Extract the first word of a string in a SQL Server query
  24. Search for one value in any column of any table inside a database
  25. Truncate Datetime to Second (Remove Milliseconds) in T-SQL
  26. Convert unix epoch timestamp to TSQL datetime
  27. What does collation mean?
  28. Aggregate SQL Function to grab only the first from each group
  29. Stored procedure hangs seemingly without explanation
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment