0

I need to insert a value into a table which only consists of one column, that is, the primary key.

Furthermore, NULL is not allowed, Identity is set to FALSE and both Identity Seed and Identity Increment are set to 0.

I try to insert with INSERT INTO table(id) VALUES (null) which obviously does not work. INSERT INTO table(id) default values also does not work.

How can I fill this column with the correctly incremented ID?

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
beta
  • 5,324
  • 15
  • 57
  • 99
  • 1
    With no identity configured, what defines "the correctly incremented ID"? – Chris Pickford Jul 25 '16 at 16:28
  • 2
    Incremented by whom? If `Identity` is false and there is no default that sets a value, your code *must* provide a value – Panagiotis Kanavos Jul 25 '16 at 16:28
  • Check this out. http://stackoverflow.com/questions/5360117/insert-and-set-value-with-max1-problems – Danieboy Jul 25 '16 at 16:29
  • No quite understanding, do you want an identity or no? – Jason B. Jul 25 '16 at 16:29
  • The table consists of incremented numbers. I do not know why `increment` is set to `0`. – beta Jul 25 '16 at 16:30
  • why can't you just alter the table to allow an identity increment? Why try to hack together something yourself when the db can do it far easier/better already? – Marc B Jul 25 '16 at 16:30
  • @Danieboy `MAX+1` is a *very* bad idea because it will can lead to different rows having the same ID if eg latest row is deleted. – Panagiotis Kanavos Jul 25 '16 at 16:30
  • because I am not allowed to change the schema. – beta Jul 25 '16 at 16:32
  • @beta are you asking how to populate a *numbers* table? That's a completely different question and you *don't* do this using any kind of default values. You write a statement that generates and inserts the required number of values. – Panagiotis Kanavos Jul 25 '16 at 16:32
  • @beta all methods used to populate a `Numbers` table can be modified to start from a non-zero value. Pick one and modify it accordingly. Check [Adam Machanic's](http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1) article which shows a simple way to generate a set of numbers – Panagiotis Kanavos Jul 25 '16 at 16:35
  • @beta please tell us more about how this table is being used. – Tim Lehner Jul 25 '16 at 18:13

3 Answers3

1

You can use while function in that insert 

declare @id int    
select @id = max(id) from table    

while @id <= (... put here max nuber of your id you want to insert)  
begin
    insert into table values (@id) 
    set @id = @id+1 end
end
Mat
  • 213
  • 1
  • 11
  • This is only safe for a single client connection. Imagine multiple inserts from different connections - max(id) is no longer reliable. – onupdatecascade Jul 25 '16 at 17:10
  • If we cant alter table and define id column as an idedntity, than maybye reasonable will be to create second table with identity column and trigger on base table that will take next iserted identity from new table? – Mat Jul 25 '16 at 17:26
1

Implementing Identity or Sequence would be the best solution, but if you really cannot alter the schema the alternative is to lock the table in a transaction, create the new value, unlock the table. Note this can have performance consequences.

create table dbo.ids ( id int primary key clustered );
GO

insert dbo.ids values  ( 1 ), ( 2 ), ( 3 ), ( 4 ) ;
GO

declare @newid int;

begin transaction

    set @newid = ( select top( 1 ) id from dbo.ids with ( tablockx, holdlock ) order by id desc ) + 1 ;

    insert into dbo.ids values ( @newid );

    select @newid;

commit

GO 20
onupdatecascade
  • 3,336
  • 22
  • 35
0

This can be a solution too.

declare @newid integer

begin tran 

select @newid = isnull(max(id), 0) + 1 from table with (xlock,holdlock)
insert into table values(@newid)
select @newid

commit tran
domenicr
  • 352
  • 3
  • 14