49

I want to know how can I use UPSERT or in other words UPDATE if records exists Else enter new record operation in SQL Server using one statement?

This example shows the ways of achieving this in Oracle Here But it uses Dual table for it which doesn't exists in SQL Server.

So, Any SQL Server Alternatives (No Stored procedures) please ?

Community
  • 1
  • 1
Maven
  • 14,587
  • 42
  • 113
  • 174
  • Check this out- http://msdn.microsoft.com/en-us/library/bb510625.aspx – Rachcha Jan 18 '14 at 19:21
  • 1
    And moreover, there's no `dual` in MS SQL Server. You can simply `SELECT` without a `FROM` clause. In Oracle it's mandatory to use a `FROM` clause, not so in SQL Server. – Rachcha Jan 18 '14 at 19:23
  • 2
    Agree with the duplicate, but please read all of the comments on all of the answers - some have better advice than others, and the highest upvoted answers aren't always the best ones, but rather the ones that have been around the longest. – Aaron Bertrand Jan 18 '14 at 19:50
  • 3
    You asked for a statement; why would you have to say "no stored procedures"? What is the purpose of saying that? You know that a stored procedure is essentially just a wrapper around one or more T-SQL statements, right? If someone did provide you with a stored procedure, you can just take the code after CREATE PROCEDURE and, voila, you don't have a procedure anymore... – Aaron Bertrand Jan 18 '14 at 20:05

1 Answers1

109

Many people will suggest you use MERGE, but I caution you against it. By default, it doesn't protect you from concurrency and race conditions any more than multiple statements, but it does introduce other dangers:

Even with this "simpler" syntax available, I still prefer this approach (error handling omitted for brevity):

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE dbo.table SET ... WHERE PK = @PK;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.table(PK, ...) SELECT @PK, ...;
END
COMMIT TRANSACTION;

More info on this UPSERT approach here:

A lot of folks will suggest this way:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
IF EXISTS (SELECT 1 FROM dbo.table WHERE PK = @PK)
BEGIN
  UPDATE ...
END
ELSE
BEGIN
  INSERT ...
END
COMMIT TRANSACTION;

But all this accomplishes is ensuring you may need to read the table twice to locate the row(s) to be updated. In the first sample, you will only ever need to locate the row(s) once. (In both cases, if no rows are found from the initial read, an insert occurs.)

Others will suggest this way:

BEGIN TRY
  INSERT ...
END TRY
BEGIN CATCH
  IF ERROR_NUMBER() = 2627
    UPDATE ...
END CATCH

However, this is problematic if for no other reason than letting SQL Server catch exceptions that you could have prevented in the first place is much more expensive, except in the rare scenario where almost every insert fails. I prove as much here:

Not sure what you think you gain by having a single statement; I don't think you gain anything. MERGE is a single statement but it still has to really perform multiple operations anyway - even though it makes you think it doesn't.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • 4
    Yes, the `TRY`-`CATCH` approach is exception handling as flow control - not good. The first approach is interesting - very cool way to avoid double reading. – J0e3gan Jan 18 '14 at 20:51
  • 1
    Will I be able to use `SCOPE_IDENTITY()` to get the ID of the updated or inserted row? https://msdn.microsoft.com/en-us/library/ms190315.aspx – Nate Anderson Sep 27 '16 at 19:02
  • Does your advice to not use MERGE change when dealing with TVP's? – StingyJack Sep 24 '18 at 00:08
  • @StingyJack No, why? When there is syntax I have good reason to avoid in *some* situations, I think it is better to use the alternative in *all* situations. Exceptions are hard to document and even harder to prevent people from learning from. – Aaron Bertrand Sep 24 '18 at 00:30