Merge Statement [Insert/update/Delete ]

CREATE TABLE [student](

      [studentID] [int] NULL,

      [studentname] [varchar](50) NULL

) ON [PRIMARY]

GO

CREATE TABLE [student_upload](

      [studentID] [int] NULL,

      [studentname] [varchar](50) NULL,

      [Action] [varchar](20) NULL

) ON [PRIMARY]

GO

/****** Object:  Table [student_upload]    Script Date: 10/21/2013 16:21:05 ******/

INSERT [student_upload] ([studentID], [studentname], [Action]) VALUES (2, N'updatedone', N'Update')

INSERT [student_upload] ([studentID], [studentname], [Action]) VALUES (3, N'updatedtwo', N'Update')

INSERT [student_upload] ([studentID], [studentname], [Action]) VALUES (5, N'ADDone', N'ADD')

INSERT [student_upload] ([studentID], [studentname], [Action]) VALUES (6, N'ADDtwo', N'ADD')

INSERT [student_upload] ([studentID], [studentname], [Action]) VALUES (4, N'stella', N'Delete')

/****** Object:  Table [student]    Script Date: 10/21/2013 16:21:05 ******/

INSERT [student] ([studentID], [studentname]) VALUES (1, N'Faizal')

INSERT [student] ([studentID], [studentname]) VALUES (2, N'Maria')

INSERT [student] ([studentID], [studentname]) VALUES (3, N'sophia')

INSERT [student] ([studentID], [studentname]) VALUES (4, N'stella')

go

Begin tran t1

CREATE TABLE Revokestudent

(

[Action] varchar(20) NOT NULL,

NewStudentID int,

Newstudentname varchar(20),

oldstudentID int,

oldstudentname varchar(20)

 );

GO

INSERT INTO Revokestudent

SELECT [Action], StudentID, studentname,oldstudentID,oldstudentname

FROM

(

MERGE [student] AS target

USING (SELECT

[studentID],[studentname],[Action] FROM [student_upload]) AS source

ON (target.[studentID] = source.[studentID])

WHEN MATCHED AND source.[Action]='update'

THEN UPDATE SET target.[studentID] = source.[studentID],

Target.[studentname] = source.[studentname]

when not matched by target and source.action='add'

Then insert ([studentID],[studentname]) values (source.studentid,source.studentname)

when Matched and source.Action='Delete'

Then  Delete

OUTPUT $action, Inserted.studentID, Inserted.studentname, deleted.studentID AS oldstudentID, Deleted.studentname AS oldstudentname)

AS Changes ([Action],studentID, studentname, oldstudentID, oldstudentname) WHERE Action IN ('update','Delete','Insert');

GO

select * from Revokestudent

--output result

Action	NewStudentID	Newstudentname	oldstudentID	oldstudentname
INSERT	5	ADDone	NULL	NULL
INSERT	6	ADDtwo	NULL	NULL
UPDATE	2	updatedone	2	Maria
UPDATE	3	updatedtwo	3	sophia
DELETE	NULL	NULL	4	stella

rollback tran t1