Duplicate Key Error

Question

question

technette asked Apr 14, '16

Duplicate Key Error

I am getting a duplicate key error when I'm trying to insert items into a table. How can I eliminate duplicate keys in the following query? INSERT INTO [DbProd].[dbo].[PART_BINARY] ([PART_ID] ,[TYPE] ,[BITS] ,[BITS_LENGTH]) SELECT Distinct P.ID ,'D' ,cast(CHAR(13) +'NOTE: Some Text.' as varbinary(max)) ,DATALENGTH(CHAR(13) +'NOTE: Some Text.') FROM PART P INNER JOIN DbApps.dbo.J85_34E94_UTAS ON dbo.ParsePart( P.ID, 1) = DbApps.dbo.J85_34E94_UTAS.[Drawing Number] LEFT JOIN DbProd.dbo.PART_BINARY PB ON PB.PART_ID = P.ID

insert duplicate values

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2016-04-14T02:11:36Z

JohnSterrett answered Apr 14, '16

Assuming PART_ID is your primary key you might already have data in the Part_Binary table that has the same PART_ID in your query used to insert new data. If this is true, add a left join PART_BINARY and then filter on PART_BINARY.PART_ID IS NULL. This should get you only data that doesn't already have an PART_ID in PART_BINARY.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

technette commented · Apr 19, 2016 at 12:02 AM

FROM DbApps.dbo.J85_34E94_UTAS UT LEFT JOIN DbProd.dbo.PART_BINARY PB ON dbo.ParsePart(PB.PART_ID,1) = UT.[Drawing Number] WHERE NOT(PB.PART_ID=NULL) I tried changing the statement and filtering the NULL value but no records are inserted.

0 ·

David Wimbush commented · Apr 19, 2016 at 10:11 AM

That's because 'something=NULL' is never true. (Or false!) You can use the IS operator to compare with NULL. Either 'NOT(PB.PART_ID IS NULL)' or, more readable I think, 'PB.PART_ID IS NOT NULL'.

0 ·

question