Can we do insertion into multiple tables in a single stretch?

Question

I would like to know the best way to insert multiple records into multiple tables at a time. Here is my situation. I have an excel file with more than 100 records. The file has all the details of users. I need to insert all the records into respective tables(aspnet_Membership, aspnet_Users, Client, ClientAddress, ClientPaymentDetails). I am using the UserId of aspnet_Membership in Client table. And other Client tables have foreign key references. Can anyone please suggest me what is the right and fastest way to insert multiple records into multiple table?

Answer 1

SPs are able to batch multiple SQL statements. so write a SP and take care of all the relationships and order of the inserts. I dont know if it is a good idea but if you have no other way to do it this way could be okay. you must make sure you are taking care off all the relationships,insert orders and possibly guids which would make your job bit harder.Cheers

Answer 2

For this scenario, I would recommend:

Place all of your data into xml....make this xml "perfect", in the sense there is no logic to be performed by the stored procedure.

Send the xml to a stored procedure.

Have the procedure shred the xml into @variable tables.

Begin a TRANsaction. Perform your CRUD operations using the @variable tables. Rollback (the entire thing) if any errors. Commit on success.

Since aspnet_Membership uses GUID/UniqueIdentifiers, you can create all your relationships on the Client Side. You don't have to wait for @@IDENTITY or "output" values after inserts.

...

Here is some xml shredding basics:

http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005.html

Here is an accepted answer, pointing to the same method.

c# - Best approach for a bulk update/merge on a table with high data volumne, but small change set