Cannot insert the value NULL into column and column does not allow nulls

Question

HI Team

i have build a physical table with some columns, where i have an identity column in my table

[ProdID] [int] IDENTITY(1,1) NOT NULL

I am trying to insert the records into this prod table using the destination table values ,insert fails

can some one please assist on this issue, really its stopping my development work,posting my error msg below

Cannot insert the value NULL into column 'ProdID', table 'Product'; column does not allow nulls. INSERT fails.".

Answer 1

When you use a column with NOT NULL then it's not possible to add NULL to the column.

I guess you have other columns as well, like:

Create TABLE Products
(
[ProdID] [int] IDENTITY(1,1) NOT NULL,
[Col2] int NULL,
[Col3] int NOT NULL,
[Col4] int NULL
)

When you want to insert a record to this table dont mention the [ProdID] column, it will be filled automaticly because its a identity.
Example:

  INSERT INTO Products ([Col2],[Col3],[Col4]) VALUES (1,2,NULL)  

Answer 2

Looks like column mapping issue in your ssis package.
It might have mapped to the column from other table or select which has null values and also check identity insert enabled or not.

Answer 3

Hi @Naresh y ,

It seems that there is a null in your data source, please have a double check.

In addition, please check the mapping, you do not need to map the column [ProdID], it will automatically create incrementing number when other columns have been assigned values.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

I have the same problem and the below solution works for me.

If you are using SSMS, you can go to your table and right click Design -> right click the ID column -> select Properties -> find and expand Identity Specification under Table Designer -> set Is Identity to true/yes -> save it and try

You can refer to this video.
https://www.youtube.com/watch?v=LWa5eFbPaB8

Answer 5

SQLSTATE[23000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot insert the value NULL into column 'price', table 'msdb.dbo.sales'; column does not allow nulls. INSERT fails. (Connection: sqlsrv, SQL: insert into [sales] ([name], [price], [quantity], [stock], [total_price], [updated_at], [created_at]) values (autem, ?, 8, 20, 236.72, 2024-07-09 09:49:50.019, 2024-07-09 09:49:50.019))