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?
3 4978 Delerna 1,134
Recognized Expert Top Contributor
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 -
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=@TVP.name
-
and Person.SurName=@TVP.SurName
-
WHERE Phone is not null and Phone<>''
-
etc
-
etc
-
Delerna 1,134
Recognized Expert Top Contributor
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 2,878
Recognized Expert Specialist
Or you can create a VIEW then use that view to cascade your update on the underlying tables.
Happy Coding!!!
~~ CK
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Greg Ofiesh |
last post by:
Anyone who can help,
I have two tables T1 and T2. T1 has fields K1 and F2 and T2 has fields
K2 and F1. F1 is the foreign key relating to K1 and F2 is the foreign
key relating to K2.
My initial question has been how can I add a row to both tables with
accurate Fx values in one SQL INSERT statement? I have been told it
cannot be done.
|
by: a |
last post by:
NEW Post
Here's my best guess at how to insert this dataset....
the code runs, but no new records are added to the sql table.
I've read and split a delimited text file into a dataset.
It looks fine in a datagrid (5 columns and 5,000 rows),
but I've been trying, without success, to then insert
the resulting dataset called "result" into a single sql
table that has an auto-increment and PK column called ID,
|
by: Sandy |
last post by:
I have four related tables. I need to do an insert on three of them. I
created a stored procedure that handles the insert on two of the tables
correctly . . . then I get to a tough part.
The third table consists of merely SomeID2 col from the second table and
SomeID4 that comes from the fourth table.
The fourth table is used to populate a checkboxlist and contains only
SomeID4 and a description.
|
by: James Radke |
last post by:
Hello,
I have a multithreaded windows NT service application (vb.net 2003) that I
am working on (my first one), which reads a message queue and creates
multiple threads to perform the processing for long running reports. When
the processing is complete it uses crystal reports to load a template file,
populate it, and then export it to a PDF.
It works fine so far....
|
by: c676228 |
last post by:
Hi everyone,
I am just wodering in asp program, if there is anybody writing store
procedure for inserting data into database since there are so many parameters
need to be passed into store procedure(assume there are many columns in the
table).
I need to insert data into two separate tables, the relation between these
two tables is 1 row of data in table1 could have multiple rows in table2
related to table1, but if the data insertion into...
| |
by: kolalakitty |
last post by:
Hopefully someone here can help me/point me in the right direction.
I've found tons of references towards making relations, creating rows,
saving said rows, using datagrids, databinding objects, and etc in
regards to Windows Forms, C#, and Visual Studio .Net 2003.
I seem to have run into a bit of a problem however. I'm not using
datagrids to show related data, my users wouldn't have any idea how to
use them, and they want to see all of...
|
by: rcoco |
last post by:
Hi,
I want to share this problem. I have a datagrid that will help me
Insert data into sql database. So I made a button On my form so that
when I press the button a new row on datagrid should be created and I
could be able to insert data. But with this code below I've failed
could someone help me and tell me where I'm going wrong:
private void Page_Load(object sender, System.EventArgs e)
{
if (! IsPostBack)
|
by: shriil |
last post by:
Hi
I have this database that calculates and stores the incentive amount
earned by employees of a particular department. Each record is
entered
by entering the Date, Shift (morn, eve, or night) and the 'employee
name'. There is another table which assigns an ID to the Shifts, i.e.
1,2 and 3 for morn, eve & night shifts respectively. From the mother
table, the incentive is calculated datewise for each employee as per
his shift duty. In...
|
by: cluce |
last post by:
I am trying to read a csv file with user info (username, password, email, address, city, zip, state, etc.) I am inserting the username, password, email into the aspnet_memberhsip table using the membership class and trying to insert the rest of the related info with a stored procedure into the custom table I created called aspnet_UserInfo using a one - one relationship by UserID but its not working. can someone look at my stored procedure for...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |