locked
inserting Data, Multiple Tables RRS feed

  • Question

  • Hi All,

     

      Have a question on inserting data into multiple tables from stored procedures. Lets say I have the following scheme,

    Table Customers

    Table Orders

    Table Order Details

     

    I have a CustomerID - Prim Key in Customers Table,

     OrderID - Prim Key and custId - Foreign Key in Orders Table

     and

     OrderDetID - Prim Key with OrdID - Foreign Key in Order Details Table.

     

      Simple enough so far. Now I create a sproc to insert data into those tables. I can't create one insert to fill all three tables correct, so I must have to do three separate inserts, one for each table in order to complete a sales order transaction, correct? It makes sense to me but was wondering if this is the normal way to insert data into joining tables when writing Stored Procedures?  You are basically doing multiple inserts to fill in the transaction details, right?

     

    J

    Thursday, June 12, 2008 11:57 PM

Answers

  • That is correct.  You need to use multiple insert statements.  Just make sure you start at the Customer level and work your way down.  You may want to wrap it in a transaction where all inserts are committed at once.

     

    Friday, June 13, 2008 1:36 AM

All replies

  • That is correct.  You need to use multiple insert statements.  Just make sure you start at the Customer level and work your way down.  You may want to wrap it in a transaction where all inserts are committed at once.

     

    Friday, June 13, 2008 1:36 AM
  • Thanks Ken for the confirmation. Now for one other question. After I do the first insert into Customers table, how can I get

    the Primary key ID, without using a select, so I can populate the foreign key in Orders table? I'm sure there is a cleaner way to get primary key from Customers table then doing a select right after the insert right?

     

     

    Friday, June 13, 2008 2:05 AM
  • Select @@Identity returns the last Identity inserted.

     

    http://msdn.microsoft.com/en-us/library/aa933167(SQL.80).aspx

     

    Friday, June 13, 2008 2:10 AM
  • Thanks Ken

     

    Friday, June 13, 2008 10:33 AM