UPSERT functionality

It UPDATEs rows if they exist in the selected row set or INSERTs rows if they don’t exist in one Sql command.

SQL Server 2005 does not have this feature. You will need 2 queries to get the functionality.

Here is a simple example

--Staging table

CREATE TABLE Staging

(ID int, Color varchar(10))

GO

--Real table

CREATE TABLE RealTable

(ID int, Color varchar(10))

GO

--Populate Staging table

INSERT INTO Staging VALUES (1, 'Red') , (2, 'Blue') , (3, 'Green')

GO

--UPSERT Functionality

--Perform UPDATE first

UPDATE RealTable

SET Color = S.Color

FROM RealTable AS RT (NOLOCK)

INNER JOIN Staging S (NOLOCK) ON S.ID = RT.ID

GO

--Perform INSERT

INSERT INTO RealTable(ID,Color)

SELECT ID, Color FROM Staging AS S

WHERE NOT EXISTS (SELECT * FROM RealTable AS RT WHERE RT.ID = S.ID)

GO

SELECT * FROM RealTable

GO

--Test the update

UPDATE Staging SET Color = 'Pink' WHERE ID = 3

GO

UPDATE RealTable

SET Color = S.Color

FROM RealTable AS RT (NOLOCK)

INNER JOIN Staging S (NOLOCK) ON S.ID = RT.ID

GO

--Perform INSERT

INSERT INTO RealTable(ID,Color)

SELECT ID, Color FROM Staging AS S

WHERE NOT EXISTS (SELECT * FROM RealTable AS RT WHERE RT.ID = S.ID)

GO

SELECT * FROM RealTable

GO

DROP TABLE RealTable

GO

DROP TABLE Staging

GO