0

I want to insert @id value into Items table as auto increment.

Table Name= Items Columns= Code, Name.

The column Code does not allow null value and is unique but it has no auto increment, I tried to write query which will fill value (1,2,3,4,...) in column of Code as auto increment but it does not work

This is my query

DECLARE @id INT 
SET @id = (select MAX(Code) from Items)

SET @id =@id+1

insert into Items (Code,Name) values(@id,'m')

This is the error

Msg 515, Level 16, State 2, Line 6 Cannot insert the value NULL into column 'Code', table 'Items'; column does not allow nulls. INSERT fails. The statement has been terminated.

I want @id to be inserted in column of Code as auto increment.

Please anyone can help me.

Schadrack Rurangwa
  • 413
  • 12
  • 28

2 Answers2

2

Can I assume your table is empty?

In this case,

SET @id = (select MAX(Code) from Items)

is NULL,

so 

SET @id =@id+1

is also null.

The correct code would be:

SET @id = (select ISNULL(MAX(Code),0) from Items)

which will return numeric 0 when there are no rows.

Typical beginner SQL error - be aware of the "poisonous" nature of NULL in SQL, which turns everything it touches into - NULL.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • There is a risk MAX(Code) / @id changes between the select and the insert – paparazzo Apr 11 '16 at 18:44
  • Yes and no. This question is not about locking. There are a TON of scenarios where this is not a problem - from code that runs under serializable locking to code the runs in single threaded ETL processes. I answered the question asked, assuming the OP knows the site rules (provide minimal example code to replicate, IGNORING ALL OTHER ELEMENTS). – TomTom Apr 11 '16 at 21:23
  • And there are *tons* of scenarios where it is a problem. OP has a score of 8 with a basic question and you assume they understand site rules and locking? – paparazzo Apr 11 '16 at 21:33
0

If you use @id you risk it changed between the two statements

Since below is one statement you don't need to wrap it in a transaction 

insert into Items (Code, Name) 
select MAX(Code) + 1, 'm'
  from items

And I think this deals with empty items 

insert into Items (Code, Name) 
select isnull(MAX(Code), 0) + 1, 'm'
  from items
paparazzo
  • 44,497
  • 23
  • 105
  • 176