0

This is Regarding MSSQL auto increment feature and i have following table created

CREATE TABLE Order(
[order_id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL,
[original_order_id] [int] NOT NULL
)

In here i have a situation where i need to insert the auto generated value for original_id to original_order_id.

After googling few minutes following thing and it works fine for me

insert into Order values('Vegitable Order', IDENT_CURRENT('Order'))

I am using java application spring JDBC templates to execute the quires. can there be any issues? specially in multi threaded environment?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
keth
  • 793
  • 2
  • 11
  • 36
  • possible duplicate of [How to get the insert ID in JDBC?](http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc) – Mark Rotteveel Mar 26 '14 at 09:23

1 Answers1

1

Using IDENT_CURRENT is not a good idea. If there are concurrent transactions, the returned value might not be your last inserted id, but the last inserted id of another transaction!

Use the JDBC getGeneratedKey facility to retrieve the generated id of the first INSERT and use the retrieved value in the second INSERT, or use SCOPE_IDENTITY() in the second INSERT. With SCOPE_IDENTITY() be sure that you are executing both statements in the same transaction.

See also: How to get the insert ID in JDBC?

Community
  • 1
  • 1
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Hello Mark, Thank you very much for your answer. Here i can not use getGeneratedKey, because there are no 2 inserts. i want to save this auto generated value in to some other column in a same table. If i use getGeneratedKeys i need to do a separate update on same table to update that column. Please let me know your thoughts – keth Mar 26 '14 at 10:00
  • @keth I somehow missed that. Why would you want to save the identity column in a different column of the same table? That is unnecessary duplication of information; simply disallow changes of the identity column. – Mark Rotteveel Mar 26 '14 at 10:17