How to Insert auto increment ID where column not auto_increment

Question

I am trying to insert some Data in a Table where the ID is int. The Id is unique and I have no idea why the developer of this software have not set this field to auto_increment

So normaly this would work, because with auto_increment I would exlude the ID and it would be filled automatically.

Insert into [dbtest].[dbo].[tArtikelAttribute]
([kArtikel] ,[cName] ,[cValue])
Select [kArtikelAttribute], kArtikel, 'han', cHan 
from [dbtest].[dbo].[tartikel] Where cHan is not NULL

I getting this error

Msg 515, Level 16, State 2, Line 2 The value NULL can not be inserted into the kArtikelAttribute column dbtest.dbo.tArtikelAttribute table. The column does not allow nulls. Error in INSERT. The statement has been finish

The table [tArtikelAttribute] is
ID (PK, int, Not Null)
kartikel (int, null)
cname (varchar(255), null)
cvalue (varchar(4000), null)

I wouldn´t want to change the fields attributes so I do not interfere with the software functionalitys Does anyone know how to do this?

thanks

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

I couldnt post this as a comment as I dont have enough reputation to perform that action.

Try looking at this link : SQL Server, How to set auto increment after creating a table without data loss?

Might be helpful.

edited May 23 '17 at 11:43

Community

1
1

answered Mar 15 '15 at 17:07

Posiedon

134
1
8

score 1 · Answer 2 · answered Mar 15 '15 at 17:12

I found the answer, thanks for your help using row_number()

 Insert into [eazybusiness].[dbo].[tArtikelAttribute]
  ([kArtikelAttribute], [kArtikel] ,[cName] ,[cValue])
  Select (Select max([kArtikelAttribute]) 
  from [eazybusiness].[dbo].[tArtikelAttribute]) 
      + ROW_NUMBER() OVER (ORDER BY kArtikel), kArtikel, 'han', cHan 
  from [eazybusiness].[dbo].[tartikel] Where cHan is not NULL

How to Insert auto increment ID where column not auto_increment

2 Answers2