Stored procedure to add data to mutiple tables

hi all

I'm an SQL newbie and I want to add data from a form into 3 different tables when the form is submitted.

I want to add data to a "Customers" table but I also want to add data to an "Orders" table that has a "Customer ID" I will only know the Customer ID after Ive added data to the "Customers" table. I also want to add data to another table "Extras" that will also require the Order ID from the "Orders" table. But again, I will only know that ID after the data has been added to the "Orders" Table.

I'm guessing that the process will be something like this

Insert [data] into Customers
Return @@Identity

Insert [data] into Orders including the Customer ID (the value of the Idenity from above)
Return @@Identity

Insert [data] into Extras including the Order ID (the value of the Idenity from above)

Done

Can someone please elaborate or put me straight?

Thanks

gilkesyAsked:
Who is Participating?
 
rafranciscoCommented:
Here's how your stored procedure might look like:

CREATE PROCEDURE SaveOrders ( @CustomerName VARCHAR(50), @ProductID INT, @OtherData VARCHAR(50))
AS
INSERT INTO Customers (Name) VALUES (@CustomerName)

INSERT INTO Orders (CustomerID, ProductID) VALUES (SCOPE_IDENTITY(), @ProductID)

INSERT INTO Extras (OrderID, OtherData) VALUES (SCOPE_IDENTITY(), @OtherData)
0
 
Brian CroweDatabase AdministratorCommented:
what language is your app?
0
 
rafranciscoCommented:
Your process is correct.  You first insert to Customers table, get the IDENTITY value generated from it (you can also use SCOPE_IDENTITY(), just to be sure you get the correct IDENTITY value from Customers), then use that IDENTITY value to insert into Orders table.  From there, get the IDENTITY value again and use that in inserting into Extras table.

You are on the right track.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
gilkesyAuthor Commented:
The application is an ASP.net web page.
0
 
gilkesyAuthor Commented:
How do I get and then use the IDENTITY value?
0
 
gilkesyAuthor Commented:
Thanks rafrancisco

0
 
gilkesyAuthor Commented:
One other thing:

After all this data has been added, the user is taken to another page to put in payment details.

If these details are not put in, then the whole "transaction" is void.

That means the records Ive added need to be deleted.

How can I approach this?
0
 
rafranciscoCommented:
I suggest collecting all the information first before creating any records in the database.  Once you've collected all the information you need, then call the stored procedure to create the records in the database.
0
 
gilkesyAuthor Commented:
OK, thats good advice.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.