help with multi statement with scope_identity()

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 2008 Forums

Transact-SQL (2008)

help with multi statement with scope_identity()

Author

Topic

Wrangler
Starting Member

35 Posts

Posted - 2012-08-08 : 14:34:35

Greetings, I have over 100 records to load into the Project table (below) but I need to capture the Identity Column right after the insert to update the SystemsToProject table. Anyway to do this without a Cursor?

DECLARE @ProjID int

INSERT INTO [Project]
([ProjectName]
,[ProjectTypeID]
,[ProjectStatusID]
,[ProjectWkGrpID])
VALUES
('project name1',1, 2,2)

select @ProjID = SCOPE_IDENTITY()

INSERT INTO [SystemstoProject]
([SystemsID]
,[ProjectID])
VALUES
(95 ,@ProjID)
GO

Thank you,

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 14:47:26

You can use the OUTPUT clause like shown below. Replace the YourIdentityColumn with the actual identity column name.

INSERT INTO [Project]
([ProjectName]
,[ProjectTypeID]
,[ProjectStatusID]
,[ProjectWkGrpID])
OUTPUT 
	95,INSERTED.YourIdentityColumn
INTO [SystemstoProject]
	([SystemsID],[ProjectID])
VALUES
('project name1',1, 2,2)

Wrangler
Starting Member

35 Posts

Posted - 2012-08-08 : 15:00:14

That worked perfect. Thank you.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 15:12:45

Very welcome. Glad to help! .)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources