[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1277
  • Last Modified:

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
3 Solutions
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 );
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!

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now