Get last inserted id

Question

Is there any way I can get the last inserted ID if I am using SQL Server CE? I have 2 tables and when a new record is created, I want to be able to save the ID in the second table too.

marc_s · Accepted Answer · 2012-01-17 20:28:33Z

13

SELECT @@IDENTITY

will retrieve the last auto-generated identity value in SQL Server.

edited Jan 17, 2012 at 20:28

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 17, 2012 at 20:05

ron tornambe

10.8k8 gold badges38 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aboodred1 · Accepted Answer · 2012-01-17 20:07:06Z

I hope this example will help you.

INSERT INTO jobs (job_desc,min_level,max_level) VALUES ('A new job', 25, 100); SELECT job_id FROM jobs WHERE job_id = @@IDENTITY;

Raphaël VO · Accepted Answer · 2012-01-17 20:05:08Z

use this query:

INSERT INTO Persons (FirstName) VALUES ('Joe'); SELECT ID AS LastID FROM Persons WHERE ID = @@Identity;

or you can view this link for more info: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Brissles · Accepted Answer · 2012-01-17 20:05:24Z

1

Assuming higher values of id are always newer, how about:

SELECT TOP 1 id FROM your_table ORDER BY id DESC

or:

SELECT MAX(id) FROM your_table

answered Jan 17, 2012 at 20:05

Brissles

3,89126 silver badges32 bronze badges

2 Comments

Joel C Over a year ago

That doesn't account for possible concurrency issues. You're introducing a race condition, hoping your SELECT MAX(id) is executed before another record gets inserted. I'd go with @@Identity since SCOPE_IDENTITY() isn't supported in SQL CE.

Ash Burlaczenko Over a year ago

@@Identity assumes the select is run in the same session. It's not clear from the OP that is the case so I think this is the better answer for the information given.

Geno · Accepted Answer · 2012-07-31 08:13:55Z

-1

This worked fine for me

SELECT TOP (1) your_id FROM your_table ORDER BY your_id DESC

answered Jul 31, 2012 at 8:13

Geno

114 bronze badges

2 Comments

Gene S Over a year ago

This may work but it is much slower than the approved answer.

VoidKing Over a year ago

Also, I think this won't account for possible concurrency issues, like that stated in the first comment in Brissles's answer.

Collectives™ on Stack Overflow

Get last inserted id

5 Answers 5

Comments

Comments

Comments

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

2 Comments

2 Comments

Related