UPDATE if exists else INSERT in SQL Server 2008 [duplicate]

Question

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 ?

Answer 1

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:

• Use Caution with SQL Server's MERGE Statement
• What To Avoid If You Want To Use MERGE
• SQL Server UPSERT Patterns and Antipatterns

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:

• Please stop using this UPSERT anti-pattern

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:

• Checking for potential constraint violations before entering TRY/CATCH
• Performance impact of different error handling techniques

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.