Share via

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

Naresh y 146 Reputation points
Oct 3, 2022, 10:35 AM

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.".

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,075 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,601 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,663 questions
{count} vote

5 answers

Sort by: Most helpful
  1. Wilko van de Velde 2,226 Reputation points
    Oct 3, 2022, 12:53 PM

    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)  
    
    1 person found this answer helpful.

  2. Hafeez Uddin 296 Reputation points
    Oct 3, 2022, 2:32 PM

    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.

    0 comments No comments

  3. ZoeHui-MSFT 38,066 Reputation points
    Oct 4, 2022, 2:22 AM

    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.


  4. Wong Ke Ryn 1 Reputation point
    Oct 14, 2022, 2:43 AM

    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

    0 comments No comments

  5. Muhammad Shoaib 0 Reputation points
    Jul 9, 2024, 9:50 AM

    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))

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.