Thursday, September 17, 2009

Merge Statement SQL Server 2008

MERGE statement is introduced in SQL Server 2008 that can greatly enhance the functionality for Data Warehouse and the staging servers.

The Merge statement allows you to perform DML actions on target table based on whether
or not a row matches information found in source table.

A common Merge scenario is moving data from one table to another.

Like User and UserHistory

User Table :

CREATE TABLE [dbo].[User]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Age] [int] NOT NULL,
[Is_Active] [bit] NULL,
[CREATE_DATE] [date] NOT NULL
)

GO

ALTER TABLE [dbo].[User] ADD CONSTRAINT [DF_User_CREATE_DATE] DEFAULT (getdate()) FOR [CREATE_DATE]

GO

AND
UserHistory Table is like:

CREATE TABLE UserHistory
(
HistoryId INT IDENTITY(1, 1),
Id INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Age INT NOT NULL,
Is_Active BIT NULL,
Create_Date DATE NOT NULL
)

------------------------------------------------------------------------------------

Now insert some data in user table and try running this query more than once and look
on the results that are produced by OUTPUT and $action clauses:

MERGE INTO UserHistory AS UH
USING [User] as U
ON U.Id = UH.Id
WHEN MATCHED THEN
UPDATE SET UH.Id = U.Id, UH.Name = U.Name
,UH.Is_Active = U.Is_Active, UH.Create_date = U.Create_date
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, Name, Age, Is_Active, Create_Date)
VALUES (Id, Name, Age, Is_Active, Create_Date)
WHEN NOT MATCHED BY SOURCE THEN
Update SET IS_ACTIVE = 0
Output $action, inserted.*;

-------------------------------------------------------------------------------------
You can perform insert, update and delete operation by matching the Source Table (User)
and the Target Table (UserHistory) in a single operation and can have the details about the
action from OUTPUT clause and using $action you can know the operation performed

Wednesday, September 16, 2009

SQL Server 2008:Output Clause Sample

So with SQL Server 2008 we have output clause available inside the query that means we can access the inserted, deleted or updated value form the output clause inside a Stored Procedure and we need not to write a trigger

Ex.
Suppose we have a user table on inserting the data we need to insert the same value to a table type variable, we can accomplish this task as given below:

DECLARE @User TABLE (ID INT, Name VARCHAR(50), Age INT, Is_Active BIT, CREATE_DATE DATETIME)
INSERT INTO [User] (Name, Age, Is_Active, CREATE_DATE)
OUTPUT inserted.* INTO @User VALUES ('TESTING', '26', 1, GETDATE())

SELECT * FROM @User