[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored procedure to add data to mutiple tables

Posted on 2005-04-25
9
Medium Priority
?
201 Views
Last Modified: 2010-03-19
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

0
Comment
Question by:gilkesy
  • 5
  • 3
9 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13861482
what language is your app?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13861484
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
 

Author Comment

by:gilkesy
ID: 13861563
The application is an ASP.net web page.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:gilkesy
ID: 13861628
How do I get and then use the IDENTITY value?
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 1000 total points
ID: 13861661
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
 

Author Comment

by:gilkesy
ID: 13861721
Thanks rafrancisco

0
 

Author Comment

by:gilkesy
ID: 13861782
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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13861808
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
 

Author Comment

by:gilkesy
ID: 13861842
OK, thats good advice.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question