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.
In the current version of PostgreSQL INSERT , we can achieve a basic upsert by specifying the conflict target (in this case id , the primary key column) and what we want to do if a conflict is detected (in this case, update the existing row):
Upsert queries are conceptually simple, consisting of a set of keys and then one or more key/value pairs for each key. Each set of key/value pairs is for a single row in the table.
A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether condition matches. It was officially introduced in the SQL:2003 standard, and expanded in the SQL:2008 standard.
The Upsert method is similar to the Synchronize method with one exception; the Upsert method does not delete any records. The Upsert method will result in insert or update operations. If the record exists, it will be updated. If the record does not exist, it will be inserted.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With