SQL Server 2008 : Table Valued Parameter and Merge

Recently I was working on one project, and I was supposed to Insert/Update many records in the database. I wanted to do it in a single round trip. I Googled it and found two excellent features of SQL Server 2008: the Table Valued Parameter and Merge Statement.

Table Valued Parameter allows you to send many records to the database in a single round trip. To know how it can be done, please refer to this link.

Merge Statement is another good thing that you’ll fall in love with. It provides an efficient way to perform multiple DML operations. 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
--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

I hope this will be helpful.

Enjoy.. 😉





2 responses to “SQL Server 2008 : Table Valued Parameter and Merge”

  1. Dhiraj Gupta Avatar

    Nice One, and good links!

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: