By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,616 Members | 1,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,616 IT Pros & Developers. It's quick & easy.

Inserting into multiple related tables using table valued parameters.

P: 1
How would I insert into multiple related tables using a table valued parameter? Is there any way to do this without using a WHILE loop?

I know how to insert from a table valued parameter into one table:
INSERT INTO dbo.Table
SELECT * FROM @TVP

But what do I do when I want to insert all of someone's information into multiple tables, and have those records related to each other by foreign keys? For example, I want to insert their address info into the dbo.Address table, their phone info into the dbo.Phone table, and then the PKs for the Phone record and the Address record should be inserted in the dbo.Person table, along with FirstName and LastName. How do I get those PKs and insert them into the Person table, all as part of inserting with a table valued parameter that has multiple records in it?
Jan 16 '10 #1
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
You will need to write multiple insert statements (one for each table to be inserted to) and select the appropriate fields from @TVP for each different insert.

You will need to join @TVP to the previously inserted table to get the primary key to insert into the next table.

for example, something like this
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO dbo.Person
  2. SELECT Name,Surname FROM @TVP
  3.  
  4. INSERT INTO dbo.Phone
  5. SELECT Person.PersonID,Phone 
  6. FROM @TVP
  7. JOIN dbo.Person on Person.Name=@TVP.name 
  8.   and  Person.SurName=@TVP.SurName 
  9. WHERE Phone is not null and Phone<>''
  10. etc
  11. etc
  12.  
Jan 18 '10 #2

Delerna
Expert 100+
P: 1,134
I generally try not to give advise that was not asked for because your design is your choice . However, you may want to give further consideration to normalizing your tables.
Jan 18 '10 #3

ck9663
Expert 2.5K+
P: 2,878
Or you can create a VIEW then use that view to cascade your update on the underlying tables.

Happy Coding!!!

~~ CK
Jan 20 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.