SQL Server 2008 MERGE

MERGE statement performs insert,update or delete operations on target table based on the results of a join with source table.

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

--Merge statement

MERGE RealTable RT

USING

(SELECT ID, Color FROM Staging) S

ON (RT.ID = S.ID)

WHEN MATCHED THEN --update rows when matched

UPDATE SET RT.Color = S.Color

WHEN NOT MATCHED BY TARGET --insert if rows donot exist in the target table

THEN INSERT VALUES (ID, Color)

WHEN NOT MATCHED BY SOURCE --delete if rows donot exist in the source table

THEN DELETE;

GO

SELECT * FROM RealTable

GO

--Test the update

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

GO

MERGE RealTable RT

USING

(SELECT ID, Color FROM Staging) S

ON (RT.ID = S.ID)

WHEN MATCHED THEN --update rows when matched

UPDATE SET RT.Color = S.Color

WHEN NOT MATCHED BY TARGET --insert if rows donot exist in the target table

THEN INSERT VALUES (ID, Color)

WHEN NOT MATCHED BY SOURCE --delete if rows donot exist in the source table

THEN DELETE;

GO

SELECT * FROM RealTable

GO

--Test the delete

DELETE FROM Staging WHERE ID = 2

GO

MERGE RealTable RT

USING

(SELECT ID, Color FROM Staging) S

ON (RT.ID = S.ID)

WHEN MATCHED THEN --update rows when matched

UPDATE SET RT.Color = S.Color

WHEN NOT MATCHED BY TARGET --insert if rows donot exist in the target table

THEN INSERT VALUES (ID, Color)

WHEN NOT MATCHED BY SOURCE --delete if rows donot exist in the source table

THEN DELETE;

GO

SELECT * FROM RealTable

GO

DROP TABLE RealTable

GO

DROP TABLE Staging

GO

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

SQL Server 2008 - Policy Based Management

After lots of confusion between DMF (Dynamic Management Functions) and DMF (Declarative Management Framework), a new name is given to be clear – Microsoft SQL Server 2008 Policy Based Management.

Policy Based Management allows to define and enforce the polices for configuring and managing SQL Servers across the enterprise. Here is just a brief description of the terminology.

Target – Object to be managed

Facet – Property of the object(target)

Condition – list of criteria for target’s facets

Policy - Set of conditions on the facets of a target

Evaluation Modes

On Demand – policy is evaluated only when manually run by admin

On change: Prevent – DDL triggers prevent policy violations

On Change: log only – Event notifications are used to check the policy when changes are made

On Schedule – SQL Agent job checks the policies for periodical violations