September 7, 2009 at 5:47 pm
I've got two tables; one for order and another for order details
The basic structure is
order (orderid int not null identity(1,1),
ordercreator varchar(50) )
orderdetails
(orderdetailsid int not null identity(1,1)
orderid int,
orderdetail varchar(max))
orderid and orderdetails id are primary keys in the respective tables.
additionally they are related through the orderid field.
what i want to do is insert a new record in the order table and also insert the orderdetail information within the same stored procedure. I've read that @@identity is not the best option to go and OUTPUT is best. Does anyone have a clear syntax for how i would go about inserting the information such that everytime the stored procedure is run, the orderids inserted in both tables always match up? I've read a lot of long winded and inherently confusing articles and would really appreciate if anyone had a clear solution to this. Cheers
September 7, 2009 at 7:05 pm
If you elect to use a trigger or a single procedure to insert into the 2nd table, read the following.
SQL Server 2005 Books Online (September 2007)
SCOPE_IDENTITY (Transact-SQL)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/eef24670-059b-4f10-91d4-a67bc1ed12ab.htm
SCOPE_IDENTITY returns values inserted only within the current scope;
The BOL page includes excellent examples of what you appear to require.
Suggest you read it / test it and determine if it will meets your need.
Here is a extremely simple example of using SCOP_IDENTITY
CREATE TABLE #order (orderid int not null identity(1,1),
ordercreator varchar(50) )
GO
CREATE TABLE #orderdetails
(orderdetailsid int not null identity(100,1) --initalized to 100 to illustrate difference between the tables
,orderid int,
orderdetail varchar(max))
GO
DECLARE @ocreator VARCHAR(50)
DECLARE @odetail VARCHAR(Max)
SET @ocreator = 'them'
SET @odetail = 'some long winded item being order or who knows what'
INSERT INTO #order (ordercreator)
VALUES(@ocreator)
INSERT INTO #orderdetails(orderid,orderdetail)
VALUES(Scope_identity(),@odetail)
SELECT * FROM #order
SELECT * FROM #orderdetails
DROP TABLE #order
DROP TABLE #orderdetails
Ouput:
orderid ordercreator
1 them
orderdetailsid orderid orderdetail
100 1 some long winded item being order or who knows what
September 8, 2009 at 9:50 am
The nice thing about the OUTPUT clause it that it doesn't require a trigger. A way to do what you are asking would be something like this:
DECLARE @orders TABLE (order_id INT IDENTITY(1,1), customer VARCHAR(10))
DECLARE @order_items TABLE (order_item_id INT IDENTITY(1,1), order_id INT, quantity INT)
DECLARE @stage TABLE (order_id INT)
INSERT INTO@orders (
customer
)
OUTPUT
inserted.order_id
INTO
@stage
VALUES
(
'Cust1'
)
INSERT INTO @order_items (
order_id,
quantity
)
SELECT
order_id,
12
FROM
@stage
SELECT
*
FROM
@orders O JOIN
@order_items OI ON
O.order_id = OI.order_id
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2009 at 9:54 am
The output clause is definitely one of the best new features in 2005.
Like others have already posted, I'd output into a temp table (or table variable), and then use that to feed sub-tables. I've done that many times, and it works quite well. (I use temp tables with it, because if I roll back the original insert, I want the insert into the output table to be rolled back too, and table variables don't work that way.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply