Reputation: 39

Insert and select in different queries SQL

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.

Upvotes: 3

Answers (8)

Tamkeen

Reputation: 448

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

Upvotes: 0

Andrey Gurinov

Reputation: 2885

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()

Upvotes: 2

Likurg

Reputation: 2760

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.

Upvotes: 0

arun

Reputation: 3677

use this, but it works for PHP

$id = mysql_insert_id();

and for c#

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

Upvotes: 1

daryal

Reputation: 14919

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

SELECT SCOPE_IDENTITY()

Upvotes: 0

Diego

Reputation: 36126

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

Upvotes: 2