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

Question

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.

Answer 1

Sure:

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

Answer 2

Sql Server 2005/2008

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

Answer 3

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.

Answer 4

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