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!