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.