CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
Laziale
Laziale

Reputation: 8225

Upsert SQL query

I am looking for some advice how to optimize a couple of SQL stored procedures. With the 1st query I am doing insert, and with the 2nd one I am doing update, if the data exists.

What I want to do is merge both stored procedures in one, where the query will check if the data exists than update, else insert a new row.

Here is what I have at this time:

update SP:

ALTER PROCEDURE [dbo].[UpdateStep1](@UserId nvarchar(50), @First_Name nvarchar(50), @Last_Name nvarchar(50),
                             @TitlePosition nvarchar(30))                                                   
AS
BEGIN
UPDATE Company_Information
SET First_Name = @First_Name,
    Last_Name = @Last_Name,
    Title_Position=@TitlePosition,  
    WHERE UserId = @UserId
    END

insert SP:

ALTER PROCEDURE [dbo].[InsertStep1](@UserId nvarchar(50), @First_Name nvarchar(50), @Last_Name nvarchar(50),
                             @TitlePosition nvarchar(30))
                            
AS
BEGIN
    INSERT INTO Company_Information(UserId,
                                    First_Name,
                                    Last_Name,
                                    Title_Position)
                                    
                                    VALUES
                                    (@UserId,
                                    @First_Name,
                                    @Last_Name,
                                    @TitlePosition)
END

So, I would like to merge both SP in one, and the SP to check if there is already data for that UserId than update, else insert a new row.

Upvotes: 10

Views: 6243

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21756

MERGE Statement?

CREATE PROCEDURE [dbo].[MERGEStep1](@UserId nvarchar(50), @First_Name nvarchar(50), @Last_Name nvarchar(50), @TitlePosition nvarchar(30))                                                   
AS
BEGIN
MERGE Company_Information WITH(HOLDLOCK) AS T
USING(SELECT 1 S) S
ON T.UserId = @UserId
WHEN MATCHED THEN UPDATE SET 
  First_Name = @First_Name,
  Last_Name = @Last_Name,
  Title_Position=@TitlePosition  
WHEN NOT MATCHED THEN
  INSERT (UserId, First_Name, Last_Name, Title_Position)
  VALUES(@UserId, @First_Name,@Last_Name,@TitlePosition);
END

Upvotes: 18

aF.
aF.

Reputation: 66697

Follow these steps:

  1. Create a variable to test it (ex: @id)
  2. Select @id = UserId from Company_Information where UserId = @UserId
  3. If @id = @userId update, otherwise insert

As @gbn specified, be aware of concurrence issues.

Upvotes: -1

Related Questions