CODEWITHSUNDEEP
sql-servert-sql
user568171
user568171

Reputation:

How do I store a non-identity column value in SQL Server?

If I have a table with an identity column as the primary key, I would use scope_identity() to retrieve latest identity value inserted during scope. What if the primary key is not an identity column, but an nvarchar(256) with a default value of newid() to generate the value. After performing the insert, is there a function that I can use to retrieve that value and store it in some variable? I'll need to insert this value into other column ID's in other tables.

Here's an example of what I'm referring to. In the aspnet_Users table the userID is described like above, if I wanted to use the userID as a FK in my own table, would it be ok to use that autogenerated newid() value or is there a better way? If this is the best way how do I store it easily?

Thank you

Upvotes: 0

Views: 404

Answers (1)

Joseph Alcorn
Joseph Alcorn

Reputation: 2442

The OUTPUT clause of the INSERT is what you are looking for. See this MSDN article.

basically:

DECLARE @newkey TABLE (keys varchar(25));

INSERT INTO actual_table (nonAutoIncId,other1,other2,other3)
OUTPUT INSERTED.nonAutoIncId INTO @newkey
VALUES ('NewId',1,2,3)

Upvotes: 6

Related Questions