Q
Problem solve Get help with specific problems with your technologies, process and projects.

@@identity problem on converted SQL code

I am having @@IDENTITY problem =O). Being a Oracle based developer, I got used to using sequences in PL/SQL. Now I am converting some code to SQL Server, and there is a multi-thread insert situation into a table. How can I eliminate receiving the same @@identity id for that table?

I don't understand. SQL Server will generate a new and distinct value for every row inserted into a table irregardless of the number of inserts being performed or the load on the system. If you have written code in triggers that grab the value of @@identity and you are inserting more than one row of data, then simply grab the value from the inserted table instead of using @@identity. A simple example of this is as follows:

 Create table test (ID int identity, Val int not null) go Create table test2 (ID int not null) go Create trigger ti_test on test For insert As Insert into test2 (ID) Select ID from inserted go begin transaction insert into test (val) values(1) commit transaction select * from test select * from test2 begin transaction insert into test (val) values(1) commit transaction select * from test select * from test2 begin transaction insert into test (val) values(1) commit transaction select * from test select * from test2 begin transaction insert into test (val) select val from test commit transaction select * from test select * from test2 go

 

For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close