I have a database that holds our inventory. I use a JOIN to load data from the Items table and the Salesinfo table into a dataset. These are linked by a SalesIndexPK in the SalesInfo table and a SalesIndexFK in the Items table.
I am writing an application that allows a user add a new item to the database. I use SaleIndexPK as a unique identifier for SalesInfo table, and use ItemIndexPk as a unique identifier in the Items table. I place all the data collected for both tables into a clone of the dataset produce by the JOIN statement used above.
How can I insert the data from the dataset into both tables, making sure that the SalesIndexPK is the SalesIndexFK in the Items table?
My line of thinking is that I would need to use two INSERT statements, inserting into the SalesInfo table first, then retrieve the SalesIndexPK to insert into the Items table along with the Items table's data.
Because my database is fairly new, all the Items.SalesIndexFK = Items.ItemIndexPK. So if I add a row to Items, I could add a row to SalesInfo and the keys would match up. But I do not want to rely on this, as I am sure the tables will not always be in sync.
Is there another way to do this? Am I missing something? Can an INSERT command insert into multiple tables? Do I need to split my dataset into two sets that match the structure of the tables?