get id of row just added for new insert

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

Transact-SQL (2000)

get id of row just added for new insert

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-11 : 15:08:04

I have the following:

CREATE PROC addSubCategory(@categoryName varchar(100), @parentCategory int)
AS
INSERT INTO tblSubCategories (Name, Parent)
VALUES (@categoryName, @parentCategory)

INSERT INTO tblEquipmentSchedule
(CategoryId,
subCategoryId )
VALUES
(@parentCategory,
@NEED NEW VALUE HERE)

GO

The first insert simply adds to tblSubCategories . The first field of tblSubCategories is an id column, an int that increments by 1.

I need to get that new number that was just created in tblSubCategories (the field named id) and add that to tblEquipmentSchedule - as seen above.

How can I quickly read tblSubCategories's last row and only get that one field value?

Thanks all,

Zath

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-11 : 15:10:23

INSERT....

SET @NewID = Scope_Identity()

INSERT...

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-11 : 15:19:32

So, the final solution would be this?

CREATE PROC addSubCategory(@categoryName varchar(100), @parentCategory int)
AS

DECLARE @newID INT

INSERT INTO tblSubCategories (Name, Parent)
VALUES (@categoryName, @parentCategory)

SET @newID = Scope_Identity()

INSERT INTO tblEquipmentSchedule
(CategoryId,
subCategoryId )
VALUES
(@parentCategory,
@newID)

GO

Thanks,

Zath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 15:27:43

[code]CREATE PROC addSubCategory(@categoryName varchar(100), @parentCategory int)
AS

declare @id int

INSERT INTO tblSubCategories (Name, Parent)
VALUES (@categoryName, @parentCategory)

select @id = scope_identity()

INSERT INTO tblEquipmentSchedule
(CategoryId,
subCategoryId )
VALUES
(@parentCategory,
@id)[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 15:32:31

Peter Larsson
Helsingborg, Sweden

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2006-12-11 : 15:35:48

Thanks once again everyone!

Zath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources