Thursday, November 19, 2009

SQL Server 2008 : Table Valued Parameter and Merge

Recently I was working on one project and I was supposed to Insert/Update bunch of records in database. I wanted to do it in a single round trip. I Google about it and found out two excellent features of SQL Server 2008: Table Valued Parameter and Merge Statement.
Table Valued Parameter allows you to send bunch of records to database in single round trip. To know how it can be done, please refer this link
Merge Statement is another good thing that you’ll fall in love with. It provides an efficient way to perform multiple DML operation. To know how it can be useful, please check out this link
Also check out one example of Merge here
I wanted to use Table Valued Parameters with Merge Command and I did it this way:


Create Procedure vi_GroupRightsAccess_AddUpdate
--below is the Table valued Parameter of type tv_GroupRight
--user defined table valued type created in db, and it should be read only
@tvpGrpRightAccess tv_GroupRight Readonly
As
Begin
--vi_GroupRightAccess is the table in my database
MERGE vi_GroupRightsAccess_Detail AS TargetTable
--Whatever columns you define in Using clause will be available in join
USING (SELECT GroupID,ModuleID,AccessID FROM @tvpGrpRightAccess) AS TvpSource
ON TargetTable.dtgroGroupId= TvpSource.GroupId and TargetTable.dtgroModuleId=TvpSource.ModuleId
--Perform intended operation if record matches
WHEN MATCHED THEN UPDATE SET TargetTable.dtgroAccessId = TvpSource.AccessID
--do what you want if it doesn’t match
WHEN NOT MATCHED THEN
INSERT(dtgroGroupId,dtgroModuleID,dtgroAccessID)
VALUES(TvpSource.Groupid,TvpSource.ModuleId,TvpSource.AccessID);
I hope this will be helpful.

Enjoy.. ;)