Sql Server INSERT scope problem

Question

This may have been asked before, but it's really hard to search for terms that limit the search results...

Take the following SQL snippet:

declare @source table (id int)
declare @target table(id int primary key, sourceId int)


set nocount on

insert into @target values (0,0)

insert into @source(id) values(1)
--insert into @source(id) values(2)

set nocount off

insert into @target select (select max(id)+1 from @target), s.id from @source s

select * from @target

This obviously executes without error, but now uncomment the second insert line and the following error occurs:

Msg 2627, Level 14, State 1, Line 15
Violation of PRIMARY KEY constraint 'PK__#7DB3CB72__7EA7EFAB'. Cannot insert duplicate key in object 'dbo.@target'.

I realise that the insert statement more than likely is effected against a snapshot of the @target table so (select max(id)+1 from @target) will always return a value of 1 - causing the violation error above...

Is there any way around this apart from resorting to a cursor?

Answer 1

You could use an identity column (that's exactly what they are meant for)

declare @target table(id int IDENTITY(1,1), sourceId int)

If your problem is that the select clause is "computed" before the insert is executed, there's afaik no way around this using a single SQL request

I think it's by design ; For your insertion to avoid duplicates, the index id must be computed during the insert, not during the select. This is the exact purpose of the IDENTITY keyword.

If you want to insert one select at a time, you must write separate requests (using cursors for example, but you'll lose atomicity, and will have to use proper locking keywords to avoid race conditions)

Answer 2

The way you're determining your new PK value, is a race condition waiting to happen. If your DB is under high load, and multiple records are being inserted at the same time, you're going to get unexpected results.

Why don't you just use an identity column , and let the database handle the assignment of a new primary Id ?

Or, you can create some kind of meta-table, which holds a record for every table in your database, and this record contains the next value that should be used as a primary id in the table. Then, you must make sure that every time you create a new record, you also update the next-value in your meta-table (and you should make sure that you do the appropriate locking), but, I see no added value in this approach vs making use of identity columns.

Answer 3

Change your insert statement to the following:

insert into @target select (select max(id) from @target) + (ROW_NUMBER() OVER(ORDER BY s.id)), s.id from @source s

This should work for this specific case but I would be careful about generalizing it.