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

Naresh y 146 Reputation points
2022-10-03T10:35:41.323+00:00

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.
12,767 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,458 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Wilko van de Velde 2,226 Reputation points
    2022-10-03T12:53:26.94+00:00

    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
    2022-10-03T14:32:31.773+00:00

    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
  3. ZoeHui-MSFT 33,126 Reputation points
    2022-10-04T02:22:24.877+00:00

    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
    2022-10-14T02:43:42.813+00:00

    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