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
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.. 😉
Leave a Reply