Inserting into multiple related tables using table valued parameters.

[simple simon]

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?

[Delerna]

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
[code=sql]
INSERT INTO dbo.Person
SELECT Name,Surname FROM @TVP

INSERT INTO dbo.Phone
SELECT Person.PersonID ,Phone
FROM @TVP
JOIN dbo.Person on Person.Name=@TV P.name
and Person.SurName= @TVP.SurName
WHERE Phone is not null and Phone<>''
etc
etc
[/code]

[Delerna]

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.

[ck9663]

Or you can create a VIEW then use that view to cascade your update on the underlying tables.

Happy Coding!!!

~~ CK