We help IT Professionals succeed at work.

Return the ID of a newly inserted record

zzconsumer
zzconsumer asked
on
I need to find out which ID a newly inserted Record has been assigned by the Database Server. Doing this using 'select max(fieldname) from table' after Insert or 'select max(fieldname)+1 from table' before Insert may be too unsecure; I need a 100% working solution, even if others are working on the database. What can I do?
Comment
Watch Question

BRONZE EXPERT
Commented:
here is one af many wais:

the insert should be enclosed in a stored procedure.

definiton of table should include

create table <table_name>
(
...
     [ID]  int identity( 1,1 )
...
)

where int, numeric, decimal, or bigint ( bigint works for SQL Server 2000 only ) is the data type
and body of the stored procedure should protect insert in a transaction, and following the transaction in case of success an identity value can be retrieved this way for SQl Server 7
SELECT @my_variable = @@identity
SQl Server 2000
SELECT @my_variable = IDENT_CURRENT( '<table_name>' )
at

a more complete exerpt from stored procedure bidy's code may look like this

create procedure ms_insert_with_return_record_id
                                    @values_to_insert_1 int
                                    , ...
                                    , @record_id int = 0 OUT
AS
 
BEGIN TRANSACTION

   insert into t
   (
     colunm_1
     , ...
   )
   select
       @values_to_insert_1
      ,

     SELECT @record_id = @@identity
IF @@error <> 0
BEGIN
     ROLLBACK TRANSACTION
     SELECT @record_id = 0
     return (1) -- error occurred
END
ELSE
     COMMIT TRANSACTION
     return (0) -- no error


Make sure that no value is inserted into the column that is defined as identity. IT will be generated for you, according to the definition at the moment when you created table: identity( 1, 1 ) means start from 1 and add 1 to each new number, identity ( 1, 5 ) means start from 1 and add 5 to each new generated number, as you can see identity follows this definition

identity ( seed, increment )

Author

Commented:
I don't have any experience using Stored Procedures. To be exact, I don't have much experience with SQL Server. Do you think that, if I use transactions from an external application, the result may be apropriate, too? The thing is, as usual my time is VERY limited.

Commented:
If you're using an identity property just return
SELECT @@Identity

BEGIN TRANSACTION

  insert into t
  (
    colunm_1
    , ...
  )
  select
      @values_to_insert_1
     ,

    SELECT @error=@@error
IF @error <> 0
BEGIN
    ROLLBACK TRANSACTION
    SELECT @record_id = 0
END
ELSE
BEGIN
    COMMIT TRANSACTION
    SELECT @@identity
END
RETURN @error
BRONZE EXPERT

Commented:
-I don't have any experience using Stored Procedures.
this is the only way to retrieve it in SQL Server

-To be exact, I don't have much experience with
SQL Server.
this is aprecciated and I tried to power up your app without having you to learn the, material by providing much comments and explanation

-Do you think that, if I use transactions from an external application, the result may be
apropriate, too?
hmm... please, be more specific

-The thing is, as usual my time is VERY limited.
can't help you there

Thanks
You can do it without stored procedures:

INSERT INTO table (column1, column2, ....) VALUES (value1, value2, ....) SELECT @@IDENTITY

This inserts a new record and returns ID of inserted record.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.