I wish to update a listing within the database that has someone to many relationship.

For instance, You will find over 100 subjects along with a student can choose whatever he wants. Let us say the next time that student edit his selection.

What's the way you'd update the database with this situation? Things I am accustomed to doing is remove all of the past choices and add all of the recently selected subjects. This really is pretty easy logic however i am just wondering whether there's an easy method to get this done when it comes to performance. During my situation 'delete' is really a single database call and placing all is really a loop in one database connection.

I realize we are able to give a logic to recognize the erased records and recently added records. Could it be realy worth to achieve that? What you will do for similar cases?


For brevity and simplicity, there's nothing really wrong having a remove-reinsert approach, barring foreign key hooks from child tables or triggers firing for every (re)place.

A different way to do it may be to pass through the brand new set like a table-valued parameter to some SP to Sql Server, which may then make use of a duo of queries to create the modification

delete many_table
where studentid=@studentid and someid not in (select someid from @tableparam p)

insert many_table(studentid, someid)
select @studentid, p.someid
from @tableparam p left join many_table t on p.someid=t.someid and t.studentid=@studentid
where t.someid is null

(there's a far more elegant MERGE syntax for SQL Server 2008, which is an additional option, further testing your TSQL abilities)