Insert Values Into Table, Putting Duplicates In Another Table So I posted this question yesterday.  Some of the responses I got were helpful, however it seems my issue is a bit more complex than I originally thought.

After doing some looking the reason I was getting errors with my INSERT statement was because I was having columns like this:

part_number  |  description  | information 
------------------------------------------------
331335A11       Desc1          Info1
331335A11       Desc2          Info1


Essentially, there are a number of entries that have the same value for the part_number field (which is suppose to be a UNIQUE column) but different vales for their other columns.  As such the query was trying to insert them into the database, and I have my problem.

So what I am trying to do, because I am unsure just how many records in my table have this problem, is to do the INSERT into my parts table, but every time I get a repeated part_number value, instead of inserting it into the parts table, it is instead inserted into a table called parts_duplicates which won't have the unique restriction for the part_number column (but still have all the same columns as the parts table.  From here I can analyze my incorrect data points and fix them (hopefully).

My only problem is...I have no idea where to even get started on tackling this.  In the question I posted above one of the responses suggested using MERGE and I am currently in the process of testing that, but I am wondering if there is a better way to go about this.
Here is a possible solution that seems to work and doesn't require triggers - you'd have to test it against your real data.

--Demo setup
Declare @Parts table (part_number varchar(30), description varchar(30), information varchar(30))
Declare @PartsTemp table (part_number varchar(30), description varchar(30), information varchar(30))
Declare @PartsDuplicates table (part_number varchar(30), description varchar(30), information varchar(30))

insert into @Parts(part_number,description,information) values
('331335A10', 'Desc1', 'Info1')     --Row already exists on the @Parts table

insert into @PartsTemp(part_number,description,information) values
('331335A00', 'Desc1', 'Info1'),    --No row on the @Parts table and no duplicate
('331335A10', 'Desc1', 'Info1'),    --Row already exists on the @Parts table
('331335A11', 'Desc1', 'Info1'),    --No row on the @Parts table
('331335A11', 'Desc2', 'Info1')     --Duplicate row on the @PartsTemp table


--The solution

--Common table expression to add row number to each PartsTemp row
;WITH PartsTempAndRowNumber
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY part_number ORDER BY description
            ) AS rn
    FROM @PartsTemp
    )

--Insert into @PartsDuplicates where either:
--The rn&lt;&gt;1 - meaning duplicates on the @PartsTemp table
--OR
--The part number already exists on the @Parts table
INSERT INTO @PartsDuplicates (
    part_number
    ,description
    ,information
    )
SELECT part_number
    ,description
    ,information
FROM PartsTempAndRowNumber ptarn
WHERE rn &lt;&gt; 1

UNION ALL

SELECT ptarn.part_number
    ,ptarn.description
    ,ptarn.information
FROM PartsTempAndRowNumber ptarn
JOIN @Parts pt
    ON pt.part_number = ptarn.part_number
        AND ptarn.rn = 1

--Insert rows to @Parts selecting from @PartsTemp where the part_number can't be found
--on the @PartsDuplicates table
INSERT INTO @Parts (
    part_number
    ,description
    ,information
    )
SELECT part_number
    ,description
    ,information
FROM @PartsTemp pt
WHERE NOT EXISTS (
        SELECT *
        FROM @PartsDuplicates
        WHERE part_number = pt.part_number
        )

--Verify @Parts rows
SELECT *
FROM @Parts
ORDER BY part_number

--Verify @PartsDuplicates rows
SELECT *
FROM @PartsDuplicates
ORDER BY part_number




After execution @Parts

| part_number | description | information |
|-------------|-------------|-------------|
| 331335A00   | Desc1       | Info1       |
| 331335A10   | Desc1       | Info1       |


After execution @PartsDuplicates

| part_number | description | information |
|-------------|-------------|-------------|
| 331335A10   | Desc1       | Info1       |
| 331335A11   | Desc2       | Info1       |