SQL Syntax To Get Scope_Identity And Then Use It In Insert Query

MS SQL Experts,

I am writing a SQL script for MS SQL 2000 that inserts a new object and then immediately inserts the new object's ID into a linking table. Can someone give me a quick example of how to get the scope_identity() and then use it in a following insert statement? I've tried something like:

insert into UserGroups (GroupName) values ('test'); select scope_identity() as NewUserGroupId;
insert into Users_UserGroups (UserId, UserGroupId) values ('1', NewUserGroupId); -- NewUserGroupId would be the newly inserted user group

However, I get "The name 'NewUserGroupId' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

The above is just a simple example of what I'm after. Please let me know if you need me to explain anything with more detail. Thank you so much for sharing your knowledge!

All the best!

~ C
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Hello clickclickbang,

method one
insert into UserGroups (GroupName) values ('test');
insert into Users_UserGroups (UserId, UserGroupId) SELECT '1', SCOPE_IDENTITY() ; -- NewUserGroupId would be the newly inserted user group

Aneesh R
Aneesh RetnakaranDatabase AdministratorCommented:
Method 2

declare @newGroupID  int

insert into UserGroups (GroupName) values ('test');
select @newGroupID   = scope_identity() ;
insert into Users_UserGroups (UserId, UserGroupId) values ('1', @newGroupID );

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The error is because you dont have a value in which you are storing the selected scope_identity() value.
If you execute the statement 'select scope_identity() as NewUserGroupId'
it will only output the result in the qyery analyzer.
In ordr that you store it in a variable and use it in the next statements, you need to declare a variable and get the result set into that variable.

What aneesh have mention in the Method 2 above is the rigth way i suppose.

Rahul Goel ITILSenior Consultant - DeloitteCommented:
SCOPE_IDENTITY() should be retrieved in a variable name and then can be processed.
You can use insert trigger set to the table to do the same.


You can write a SP for the same
clickclickbangAuthor Commented:
Thank you for the tips. Very simple. Very helpful!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.