CODEWITHSUNDEEP
sqlsql-servert-sqlscope-identity
OrElse
OrElse

Reputation: 9959

SQL Server: How do I get the value for the row I inserted?

My SQL Server table has, between other, these columns.

AutoID which has IdentitySpecification set to True and GuidKey which has the default value of (newid())

AutoID   GuidKey
1        f4rc-erdd
2        gedd-rrds

Is there any way of getting the GuidKey from the row inserted?

I need something like the Scope_Identity(), with the difference that i don't want to get the content of AutoID, but the GuidKey column.

Upvotes: 1

Views: 771

Answers (4)

Orson
Orson

Reputation: 15431

For the uniqueidentifier i do as follows:

DECLARE @ID uniqueidentifier
SET @ID = newId()

...

Then i use the @ID in my insert statement. Finally i return the value using SELECT @ID or RETURN @ID

Upvotes: 0

Håvard S
Håvard S

Reputation: 23876

You can do the following:

DECLARE @TempTable TABLE (GuidKey UNIQUEIDENTIFIER)

INSERT INTO YourTable(Columns) 
  OUTPUT INSERTED.GuidKey INTO @TempTable(GuidKey) 
  VALUES(YourVALUES)

This will insert the inserted GuidKey into @TempTable. You can then pull the value from that table.

Upvotes: 0

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

Sql Server 2005/2008

INSERT INTO myTable (SomeColumn)
    OUTPUT INSERTED.GuidKEY
VALUES ('SomeData')

Upvotes: 5

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

Sure:

 SELECT GuidKEy FROM [Table] WHERE AutoID= scope_identity()

Upvotes: 0

Related Questions