In SQL Server 2008 using MERGE concept we can achieve INSERT and UPDATE in a single statement.
Many times we would have faced a scenario like if the key is present in the table then update that record. If the key is not present in the table then insert that as a new record. To do this we normally have to SELECT the record and see whether it EXISTS or not to decide whether to INSERT or UPDATE. The better way which I learnt recently is to make use of @@ROWCOUNT and do this with ease.
Table Structure:
CREATE TABLE tblUpsert
(
EmpID INT PRIMARY KEY,
firstname VARCHAR(25),
lastname VARCHAR(25),
emailid VARCHAR(50)
)
GO
Normal method:
/*
This SP would work unless it isn't used in multiple threads simultaneously :)
Same SP is used to INSERT as well as UPDATE a table.
*/
CREATE PROCEDURE uspUPSert
(
@empID INT,
@fname VARCHAR(25),
@lname VARCHAR(25),
@emailid VARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM tblUpsert WHERE EmpID = @empID)
BEGIN
UPDATE tblUpsert
SET emailid = @emailid ,
firstname = @fname ,
lastname = @lname
WHERE EmpID = @empID
END
ELSE
BEGIN
INSERT INTO tblUpsert VALUES (@empID, @fname, @lname, @emailid)
END
END
GO
--Testing
EXEC uspUPSert 1, 'Amala','Paul', 'Amala@abc.com'
GO
EXEC uspUPSert 1, 'Amala','Paul', 'AmalaPaul@kollywood.com'
GO
EXEC uspUPSert 2, 'Sachin','Tendulkar', 'Sachin@india.com'
GO
SELECT * FROM tblUpsert
GO
Better or Alternate method to do UPSERT:
/*
Same SP is used to INSERT as well as UPDATE a table.
Here we are avoid unnecessary checking of whether the record exists or not.
Instead try to Update directly. If there is no record then @@RowCount would be 0.
Based on that Insert it as a new record.
*/
CREATE PROCEDURE uspUPSert_Ver2
(
@empID INT,
@fname VARCHAR(25),
@lname VARCHAR(25),
@emailid VARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRAN
UPDATE tblUpsert WITH (SERIALIZABLE)
SET emailid = @emailid ,
firstname = @fname ,
lastname = @lname
WHERE EmpID = @empID
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO tblUpsert VALUES (@empID, @fname, @lname, @emailid)
END
COMMIT TRAN
END
GO
--Testing
EXEC uspUPSert_Ver2 1, 'Amala','Paul', 'Amala@abc.com'
GO
EXEC uspUPSert_Ver2 1, 'Amala','Paul', 'AmalaPaul@kollywood.com'
GO
EXEC uspUPSert_Ver2 2, 'Sachin','Tendulkar', 'Sachin@india.com'
GO
SELECT * FROM tblUpsert
GO
Comments