To continue the previous post, this article demonstrates ways to do Upsert (update and insert) and how MERGE statement in SQL Server 2008 (or later) can be more efficient to perform the two operations at once.
First we’ll create a table for this demo.
CREATE TABLE dbo.GroupInfo ( Id int unique not null , App varchar(100) , DB bit )
We want to do update if the Id is found on the table and insert if it’s a new Id number.
1. Conventional way of doing it is by using IF EXISTS statement.
CREATE PROCEDURE [dbo].[p_UPSERT1] @ID int , @APP varchar(100) , @DB bit AS SET NOCOUNT ON; IF EXISTS (SELECT Id FROM dbo.GroupInfo WHERE Id = @ID) UPDATE dbo.GroupInfo SET App = @APP , DB = @DB WHERE Id = @ID ELSE INSERT INTO dbo.GroupInfo ( Id , App , DB ) VALUES ( @ID , @APP , @DB ) SET NOCOUNT OFF;
2. Second way of doing it is by taking advantage of the @@ROWCOUNT.
CREATE PROCEDURE [dbo].[p_UPSERT2] @ID int , @APP varchar(100) , @DB bit AS SET NOCOUNT ON; UPDATE dbo.GroupInfo SET App = @APP , DB = @DB WHERE Id = @ID IF @@ROWCOUNT = 0 INSERT INTO dbo.GroupInfo ( Id , App , DB ) VALUES ( @ID , @APP , @DB ) SET NOCOUNT OFF;
3. The third and probaby the best way by using MERGE to perform INSERT and UPDATE operations on a table in a single statement.
CREATE PROCEDURE [dbo].[p_UPSERT3] @ID int , @APP varchar(100) , @DB bit AS SET NOCOUNT ON; MERGE INTO dbo.GroupInfo AS tgt USING (SELECT @ID) AS src (id) ON tgt.Id = src.id WHEN MATCHED THEN UPDATE SET App = @APP , DB = @DB WHEN NOT MATCHED THEN INSERT ( Id , App , DB ) VALUES ( @ID , @APP , @DB ); SET NOCOUNT OFF;
Now, you can analyze the execution plan of each stored procedure on your own to compare them.
EXEC p_UPSERT1 @ID = 1 , @APP = 'App 1' , @DB = 0 GO EXEC p_UPSERT2 @ID = 2 , @APP = 'App 2' , @DB = 0 GO EXEC p_UPSERT3 @ID = 3 , @APP = 'App 3' , @DB = 0 GO
MarĂa Paz SZ says
Conciso! Gracias đŸ™‚
Ali says
Thank you
platt says
You’re welcome Ali.