Insert and select in different queries SQL

Question

I have a server and many clients, my application is on clients and database on server, i have one table

Table -->  Id --> int Auto-increment,   
Name --> nvarchar(50),

So, whenever i insert a new row from client with query

Insert into Table Name Values('NameValue')

It inserts the row and sql auto generates the Id field. So, to fetch its Id, I use the following query

Select max(Id) as maxId from Table

but both queries are on different connections

It works well when only one client is operating at a time, but when multiple clients are working, Many insert queries are requested by clients before i could request the 'getMaxId' query.

Why all answer is how to get last ID, if im not mistake Neha Khanna, can get it, and problem is get not correct ID — Likurg, Apr 19 '12 at 08:44

score 2 · Answer 1 · answered Apr 19 '12 at 08:36

2

You can use the following:

SELECT SCOPE_IDENTITY()

This selects the last-inserted identity.

answered Apr 19 '12 at 08:36

Jeroen

60,696
40
206
339

Andrey Gurinov · Answer 2 · 2012-04-19T08:44:38.323

2

Try this:

DECLARE @a TABLE (
    Id int IDENTITY (1, 1),
    b VARCHAR(1000)
)

DECLARE @b TABLE (
    Id INT
)

INSERT @a (b)
OUTPUT INSERTED.Id INTO @b
SELECT NAME 
FROM sys.objects


SELECT * FROM @a
SELECT * FROM @b

Or, you can always use that for retrieving the latest ident:

SELECT IDENT_CURRENT('TABLE_NAME')

Or use

SELECT SCOPE_IDENTITY()

edited Apr 19 '12 at 08:44

answered Apr 19 '12 at 08:36

Andrey Gurinov

2,825
1
20
23

Please check the update, it can be sipmler, but solves your problem – Andrey Gurinov Apr 19 '12 at 08:39

score 2 · Answer 3 · answered Apr 19 '12 at 08:37

2

The best way is to run a command to get the recent inserted value.

There are three commands you can run to do that.

This link will explain them

the best is

SELECT SCOPE_IDENTITY()

because if you have a table A that calls a trigger and this trigger inserts data on a table B, this command will get you the ID of the table A, while @@IDENTITY will get you the id of table B

answered Apr 19 '12 at 08:37

Diego

34,802
21
91
134

see my edit and study the differences between @@IDENTITY and SCOPE_IDENTITY(). This is very important – Diego Apr 19 '12 at 08:42

score 1 · Answer 4 · answered Apr 19 '12 at 08:36

1

Rather use:

select @@identity

instead of select max(id)...

It will return the last generated identity for the current connection.

answered Apr 19 '12 at 08:36

Philip Fourie

111,587
10
63
83

2

You need to be careful when using @@identity. Since it's across scopes you could get a value from a trigger, instead of your current statement - http://stackoverflow.com/a/42655/905651 – weenoid Apr 19 '12 at 08:43
But with every sql qery, i make a new connection – Neha Khanna Apr 19 '12 at 08:46

score 1 · Answer 5 · answered Apr 19 '12 at 08:41

1

use this, but it works for PHP

$id = mysql_insert_id();

and for c#

Int32 newId = (Int32) myCommand.ExecuteScalar();

answered Apr 19 '12 at 08:41

arun

3,667
3
29
54

score 0 · Answer 6 · answered Apr 19 '12 at 08:38

0

Add the following after inserting instead of Max(id) selection.

SELECT SCOPE_IDENTITY()

answered Apr 19 '12 at 08:38

daryal

14,643
4
38
54

score 0 · Answer 7 · answered Apr 19 '12 at 08:42

0

Make function in sql, where you will add row and get ID by SELECT SCOPE_IDENTITY(). While you call function you will get ID which is exactly added.

answered Apr 19 '12 at 08:42

Likurg

2,742
17
22

score 0 · Answer 8 · edited May 23 '17 at 11:53

0

try this hope it will may help You

  declare @table1 table(id int identity,name varchar(50))
    insert into @table1 (name) output inserted.id  values('abc') 
    insert into @table1 (name) output inserted.id values('xyz')
    insert into @table1 (name) output inserted.id values('pqr')

for more see here

edited May 23 '17 at 11:53

Community

1
1

answered Apr 19 '12 at 09:46

Tamkeen

448
4
10

Insert and select in different queries SQL

8 Answers8