CODEWITHSUNDEEP
sqlinsertauto-increment
veritaS
veritaS

Reputation: 519

How to Insert auto increment ID where column not auto_increment

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

Upvotes: 0

Views: 2469

Answers (2)

veritaS
veritaS

Reputation: 519

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

Upvotes: 1

Posiedon
Posiedon

Reputation: 162

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.

Upvotes: 1

Related Questions