Inserting into two sql tables at the same time

Hi,

I have two tables in my database, Orders and OrdersId and are laid out as following:

Orders OrdersId
------- ----------
OrderId (comp key) OrderId (PK)
LineId (comp key) RepId
ProductCode CustId
ProductPrice
ProductQuantity

The OrderId field in table OrdersId is the primary key, whereas the Orders table has a composite key (OrderId and LineId).

I need to add a row of purchased item data data into both tables but am not sure how. I want the OrdersId table should be filled first as the OrderId field is also required in the other table, but how do I take the OrderId value and fill the the Orders table including this value

Answer this question

Inserting into two sql tables at the same time

mahalax

What I would do is use an INSERT statement for each table from within a transaction. You can accomplish this in a single stored procedure and it's reasonably straightforward using Transact SQL.
You don't mentioned whether OrderID is an identity column, but if so then SELECT @@IDENTITY would need to be called after the INSERT to the OrdersID table.
Attila Soos
Yes works perfectly now - many thanks for the help!
DRoden
The article is not that clear with regard to creating a stored procedure to input into two separate tables. Do I need to create two stored procedures (one for each table)
Arkiliknam

See if the following article helps:

Implementing Referential Integrity and Cascading Actions
search and deploy
should it not be @@Scope_Identity() @@Identity is bad practice from what I read as for example triggers can trigger @@Identity, so using Scope_Identity() will give you the correct scope value for that Transaction
JohnACE

Yes, it's just SCOPE_IDENTITY() but you are correct, starting with SQL Server 2000 that is what you should be using in a stored procedure.
Inserting into two sql tables at the same time

Answer this question