Violation of PRIMARY KEY constraint on table

Question

Hi team

i have a table where it uses the identify column inside the table, trying to insert the records into the table getting the below error, and have the composite primary key in my table that is ,the below is the table definition,

Set identity_Insert [dbo].[Product] ON , i tried this but no use

CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Time] ASC,
[Product] ASC

insert condition

INSERT INTO [dbo].[Product]
(
[TIME],
Product,

SELECT distinct
CONVERT(SMALLDATETIME, CONVERT(VARCHAR(10), DATEADD(DAY , -1, GETDATE()), 102)) AS [TIME] ,
Product
From dbo.stgproduct

Violation of PRIMARY KEY constraint 'PK_Product'. Cannot insert duplicate key in object 'dbo.Product'. The duplicate key value is (Apr 19 2022 12:00AM, BACN10LJ-P4S7-2).
The statement has been terminated.

Answer 1

It is telling you, dbo.Product already has a value for (Apr 19 2022 12:00AM, BACN10LJ-P4S7-2).

You need to look at dbo.Product.

Answer 2

No the table is empty, it doesn't have that value,issue with the composite key or the identity column ,please suggest

Answer 3

Your code for the Time column are creating duplicate values.
Try this:

INSERT INTO [dbo].[Product]
(
[TIME],
Product)

SELECT distinct
CONVERT(VARCHAR(24), DATEADD(DAY , -1, GETDATE()), 126) AS [TIME] ,
Product
From dbo.stgproduct

Answer 4

Run this to find the duplicate records:

;with dup as (
SELECT distinct
CONVERT(SMALLDATETIME, CONVERT(VARCHAR(10), DATEADD(DAY , -1, GETDATE()), 102)) AS [TIME] ,
Product
From dbo.stgproduct
)
SELECT [TIME],[Product]
FROM dup
GROUP BY [TIME],[Product]
HAVING COUNT(*) > 1

Answer 5

Hi @Naresh y
First,to make sure the [Product] table is empty,please run this query:

Truncate table [dbo].[Product]  

Second, run this Insert query:

INSERT INTO [dbo].[Product]( [TIME],Product)  
SELECT distinct CONVERT(SMALLDATETIME, CONVERT(VARCHAR(10), DATEADD(DAY , -1, GETDATE()), 102)) AS [TIME] , Product  
From dbo.stgproduct  

Below is my local test which is able to insert data successfully:

create table test(id int identity(1,1),  
                  times varchar(30),employeeid int,  
                  constraint PK_Product primary key CLUSTERED (times ASC, employeeid ASC))  
--Truncate table test  
  
insert into test(times,employeeid)  
SELECT distinct CONVERT(SMALLDATETIME, CONVERT(VARCHAR(10), DATEADD(DAY , -1, GETDATE()), 102)) AS [TIME]  ,employee_id  
From employees  
  
--drop table test  

If still receive this error,please feel free to let us know.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.