i have build a physical table with some columns, where i have an identity column in my table
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.".
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)
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.
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
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))
