Help with OUTPUT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with OUTPUT

Author

Topic

shotz1015
Starting Member

4 Posts

Posted - 2009-11-22 : 22:09:58

I'm trying to use two insert statements to populate rows in two different tables. I want the second insert table to use the identity (primary key) created from the first insert statement. I read that the output clause is used for this but I'm not sure how to code it. My statement looks like this.

Insert into transactions (retail_id, transaction_date, employee_id, transaction_inactive)
Values (1, 12/12/2009, 1, 0)
Insert into product_transaction (transaction_id, product_id, transaction_added, transaction_returned)
Values (1, 20, 10)

For instance, if the transaction_id was assigned the primary key identity 50, I want the second insert statement to use 50 as the transaction_id to be inserted in the product_transaction table.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-22 : 22:13:23

Don't need output for this unless you're going to do it in 2 seperate batches.


Declare @transaction_id int

Insert into transactions (retail_id, transaction_date, employee_id, transaction_inactive)
Values (1, 12/12/2009, 1, 0)

SET	@transaction_id = scope_identity()

Insert into product_transaction (transaction_id, product_id, transaction_added, transaction_returned)
Values (@transaction_id, 1, 20, 10)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 09:50:43

also see

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources