Getting the primary key of an newly inserted row i

Question

I have a bunch of data which will insert into a table. This issue is that I need it to return the primary key to that table. I wasn't sure if there was things like:

 insert into TABLE (...) values (...) RETURNING p_key

or

 select p_key from (insert into TABLE (...) values (...))

I am making a workaround for a browser and saved information which will more or less add a row and then update it... but without the primary key, there is no way to update it as there is no reference to it.

I was looking online and found some examples via google, but it confused me slightly with these examples.

http://en.wikipedia.org/wiki/Insert_(SQL)#Retrieving_the_key

http://www.daniweb.com/web-development/databases/ms-sql/threads/299356/returning-identity-of-last-inserted-row-uniqueidentifier

Wikipedia was saying that for SQL Server 2008 to use OUTPUT instead of RETURNING, possible to use something like OUTPUT p_key

Answer 1

If you're inserting a whole set of rows, selecting the SCOPE_IDENTITY() won't do. And SCOPE_IDENTITY also only works for (numeric) identity columns - sometimes your PK is something else...

But SQL Server does have the OUTPUT clause - and it's very well documented on MSDN!

INSERT INTO dbo.Table(columns)
OUTPUT INSERTED.p_key, INSERTED.someothercolumnhere .......
VALUES(...) 

Those values will be "echoed" back to the calling app, e.g. you'll see them in a grid in SQL Server Management Studio, or you can read them as a result set from your C# or VB.NET calling this INSERT statement.

Answer 2

In C#, right after your SQL Statement write SELECT SCOPE_IDENTITY(); so your code would be:

insert into TABLE (...) values (...); SELECT SCOPE_IDENTITY();

then, instead of executeNonQuery use executeScalar.

That should do the trick!

Answer 3

Scope_Identity() is what you want, assuming that by "primary key" you mean "Identity"

declare @id int 
insert yourtable values (some, values)
select @id = Scope_Identity()

Answer 4

Using @@IDENTITY , you can get the last generated primary key

Insert into TableName (Name,Class) values('ABC','pqr')
select @@IDENTITY

Answer 5

After performing insert, query:

select scope_identity()

to retrieve last inserted primary key.