Finding Auto Increment Insert ID

  • Thread starter Thread starter John Smith
  • Start date Start date
J

John Smith

Hello all:

Is there a way to determine the auto increment id from a newly inserted
record in the database?

Thanks,

John
 
Only way I know is the use a stored procedure to do the insert. Have
the SP return the auto ID.

In SQLServer:

insert (blah) values (bleh)
RETURN @@IDENTITY

In the parameters for your SQLCommand obj. Have one whose direction is
returnValue instead of input.

This way you can retrieve the ID. But only after the record was
inserted into the DB.

No other way that I know of. Love to hear other people's suggestions
for alternate methods though.
 
Only way I know is the use a stored procedure to do the insert. Have
the SP return the auto ID.

In SQLServer:

insert (blah) values (bleh)
RETURN @@IDENTITY

In the parameters for your SQLCommand obj. Have one whose direction is
returnValue instead of input.

This way you can retrieve the ID. But only after the record was
inserted into the DB.

No other way that I know of. Love to hear other people's suggestions
for alternate methods though.
Click to expand...
Thanks for the tip. Someone else suggested that you could run a second
query after the insert which selects the new id such as:

"SELECT id FROM my_table ORDER BY id DESC"

Then the first record returned is the id in question. Of course this
has the extra overhead of a second query, but it works across all
database technologies.

John
 
Also if other people access the DB another record could be inserted in
b4 your query runs.
 
John said:
Thanks for the tip. Someone else suggested that you could run a second
query after the insert which selects the new id such as:

"SELECT id FROM my_table ORDER BY id DESC"

Then the first record returned is the id in question. Of course this
has the extra overhead of a second query, but it works across all
database technologies.

John
Click to expand...

HMMM,
Maybe SELECT MAX(id) MaxId FROM my_table

Then the ONLY record returned would be the id in question

JB ;)
 
John said:
HMMM,
Maybe SELECT MAX(id) MaxId FROM my_table

Then the ONLY record returned would be the id in question

JB ;)
Click to expand...

Just to clarify, the reason I posted this was because I have seen some
VERY nasty shiite happen when some codedonkey goes "hmm, how do I get
the next id? I know 'SELECT * FROM MyTableWith500000RecordsAnd20Fields
ORDER BY id desc'"

Why is the program so slow after its been in production for a month?

And this was EVERYWHERE.

Monkeys

JB
 
John Smith said:
Thanks for the tip. Someone else suggested that you could run a second
query after the insert which selects the new id such as:

"SELECT id FROM my_table ORDER BY id DESC"

Then the first record returned is the id in question. Of course this has
the extra overhead of a second query, but it works across all database
technologies.

John
Click to expand...

If you're going to try to do something like the SELECT statement you
mentioned above, at least minimize the performance import by using the
following:

SELECT TOP 1 id FROM my_table ORDER BY id DESC

However I'd recommend skipping that approach altogether, as I believe
someone else mentioned, it's possible someone else could insert a row before
your second query runs, giving you an eroneous result. I'd recommend the
following approach instead (this assumes your DB is SQL Server):

- put your SQL all within a stored procedure like so:

CREATE PROCEDURE my_proc

(
@val1 varchar(100),
@val2 varchar(100),
@id int OUTPUT
)
AS

INSERT INTO my_table (col1, col2)
VALUES (@val1, @val2)

SET @id = SCOPE_IDENTITY()

GO

Within your C# code then, use a SqlCommand and pass in 3 parameters, two as
ParameterDirection.Input and one as ParameterDirection.Output to catch the
new ID value. I'd say this is the most reliable way to do what you are
looking for. No it's not portable across different DB platforms but I'm
sure you can do the same sort of thing on any mature DB system (Oracle,
Sybase, DB2, etc...).

CVD
 
My understanding was that SELECT @@identity would return the last
identity for the given _connection_ and any scope and SCOPE_IDENTITY()
returns the last identity for the given connection and given scope which
could be of interest if the database used triggers. So you should be
able to wrap the insert and select scope_identity into a transaction and
get the valid ID as long as the connection is not being shared. A stored
procedure might be better solution, however.

Regards,
Jeff
 
Back
Top