Insert into Table ignoring duplicate values

Question

I am having a brain fart on figuring this out. I have the following two Tables:

Table: parts
    part_id          INT IDENTITY(1,1) NOT NULL,
    part_number      VARCHAR(50) UNIQUE NOT NULL,
    part_description VARCHAR(MAX) NOT NULL,
    information      VARCHAR(MAX) NULL,
    manufacturer_id  INT NOT NULL,
    subcategory_id   INT NOT NULL

Table: part_temp
    part_num  VARCHAR(50) NOT NULL,
    part_desc VARCHAR(MAX) NULL,
    info      VARCHAR(MAX) NULL,
    man_id    INT NULL,
    sub_id    INT NULL

part_temp is my temporary table that contains data from a CSV file, that is why only one column is set to be NOT NULL. I need to insert the data from part_temp into parts.

I have properly cleaned the data in the table so there are no null values trying to be inserted into rows which require a value. My issue however is with my UNIQUE constraint for my part_number columns in the parts table. There are duplicate values within the part_temp table so I need a way to be able to skip over them during my insert. This is what I have tried so far, but it does not work:

INSERT INTO parts
SELECT DISTINCT pt.part_num, pt.part_desc, pt.info, m.manufacturer_id, s.subcategory_id
  FROM part_temp                AS pt
       FULL OUTER JOIN man_temp AS mt ON pt.man_id = mt.man_id
       INNER JOIN manufacturers AS m  ON mt.man_name = m.manufacturer_name
       FULL OUTER JOIN cat_temp AS ct ON pt.sub_id = ct.category_id
       INNER JOIN subcategories AS s  ON ct.category_name = s.subcategory_name
 WHERE NOT EXISTS(SELECT part_number FROM parts WHERE part_number = pt.part_num)

These are the tables included in the joins not listed above

Table: man_temp
    man_id INT NOT NULL,
    man_name VARCHAR(100) NOT NULL

Table: manufacturers
    manufacturer_id   INT IDENTITY(1,1) NOT NULL,
    manufacturer_name VARCHAR(100) NOT NULL

Table: cat_temp
    category_id   INT NOT NULL,
    category_name VARCHAR(100) NOT NULL

Table: subcategories
    subcategory_id   INT IDENTITY(1,1) NOT NULL,
    subcategory_name VARCHAR(100) NOT NULL

What is it that is wrong with my INSERT query?

The specific error I am getting is:

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint. Cannot insert duplicate key in object 'dbo.parts'. The duplicate key value is (31335A11)

part_num 31335A11 appears in the csv file more than once. As such it appears in the part_temp table more than once. It would be easy if it was just this entry, but I have more than 1,000 repeat entries, so it would take forever to remove all the duplicates. Nothing exists in parts as it is a brand new empty table I am trying to put values into.

Answer 1

If I run into a Violation of UNIQUE KEY constraint the first thing I do is ask myself the following questions:

1. Does the duplicate key already exist in the target table?
2. Is the key duplicated in any source table?

From answering the above you will have a good idea of where the duplicate value is sourced from and a reasonable idea of the cause. Example possibilities are:

• Duplicate exists in source table
• Duplicate does not exist at source, but a transform of the data causes duplicates to be returned

Ultimately you need to strip this problem back to basics, as you have the duplicate value in question you need to start digging into the data and pay close attention to the source query. Luckily in your example it is a simple query and you can easily strip out joins to see if any of those introduce the duplicate, or if indeed the duplicate exists due to non-key attributes being different therefore causing your DISTINCT to not do what you expect it to do.

I guess what I am trying to say is that there isn't necessarily a quick win for this kind of problem and you need to do some good ol' fashion detective work using the clues provided by SQL.

Moot point, but I like to explicitly list the columns on an insert so that nothing breaks if the order of columns on the target table is different to that of the source query.

Answer 2

First of all: there is no CONFLICT command in SQL SERVER.

Second:

You are going to the wrong direction using DISTINCT and WHERE NOT EXISTS. Neither commands will prevent you from dupplicating data:

Suppose you have the following data on part_temp:

part_num    part_desc         info        man_id     sub_id
============================================================
000345      something1        some info   2          1
000345      something2        some info1  4          6
000345      something3        some info2  5          8

Suppose part_num 000345 does not exists in parts table; the query will try to insert the 3 records (which are distinct each other).

Finally:

For accomplish what you want. SQL Server provides a command called: MERGE

Using MERGE, you can decide what to do everytime you hit a conflict: this is either to update the previous inserted record or simply skip the new candidate for insertion

Use the following code for skip inserting conflict cases:

MERGE INTO parts A
USING (

 --YOUR SELECT QUERY HERE; NO NEED TO USE DISTINCT / WHERE

) B
ON(A.part_num = B.part_num)
WHEN MATCHED THEN UPDATE SET A.part_num=A.part_num
WHEN NOT MATCHED THEN INSERT(part_number,part_description,....) 
VALUES(B.part_num,B.part_desc,....);

Good luck!

If you want to store repeated data into a second table include the OUTPUT Clause at the end of the UPDATE statement (which works as a DO NOTHING actually)....

...
WHEN MATCHED THEN 
   UPDATE SET A.part_num=A.part_num
   OUTPUT DELETED.part_id, B.* INTO part_duplicates
WHEN NOT MATCHED THEN 
   INSERT(part_number,part_description,....) 
   VALUES(B.part_num,B.part_desc,....);