50

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 ?

Improve this question
4
  • Check this out- msdn.microsoft.com/en-us/library/bb510625.aspx Commented Jan 18, 2014 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. Commented Jan 18, 2014 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. Commented Jan 18, 2014 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... Commented Jan 18, 2014 at 20:05

1 Answer 1

Reset to default
114

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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.
Will I be able to use SCOPE_IDENTITY() to get the ID of the updated or inserted row? msdn.microsoft.com/en-us/library/ms190315.aspx
Does your advice to not use MERGE change when dealing with TVP's?
@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.