INSERT INTO SELECT CROSS JOIN Composite Primary Key

Question

I'm performing an INSERT INTO SELECT statement in SQL Server. The situation is that there are two Primary keys of two different tables, without anything in common, that are both foreign keys of a third table, forming a composite primary key in that last table. This can usually be accomplished with a cross join - for example,

Table1.ID(PK)
Table2.Code(PK)

-- Composite PK for Table3
Table3.ID(FK)
Table3.Code(FK)

INSERT INTO Table3
SELECT ID, Code
FROM Table1
CROSS JOIN Table2
WHERE Some_conditions...

I'm getting a "Cannot insert duplicate key row" error. It will not allow Table2.Code to be repeated in Table3, since it is a unique ID, even though the primary key of Table3 is Table1.ID combined with Table2.Code. Hence, the following pairs should be recognized as different PK values in Table3 for example: {1024, PSV} and {1027, PSV}.

Is there a way to fix this, or have I designed this database incorrectly? I have considered creating a third unique ID for Table3, but it is highly impractical in this scenario.

It would seem that you already have data in the table that conflicts with the new rows. — Gordon Linoff, Aug 06 '15 at 20:44
Can you post the actual table definitions and a few rows of the first two tables? There just isn't a lot of detail posted so far and it is hard to tell what is going on. — Sean Lange, Aug 06 '15 at 20:48
Is it possible you have a uniqueness constraint on one of the columns separate from the primary key? — shawnt00, Aug 06 '15 at 20:53
No, it does not - see the last sentence of the original post. — Dean Zaslow, Aug 06 '15 at 21:03
I think that a string truncation would cause a different error but is it possible that `Code` is a string that's losing its final character or something? — shawnt00, Aug 06 '15 at 21:03
I've seen people use IDENTITY columns even with other primary keys. It's a dumb idea but they're not the same thing. — shawnt00, Aug 06 '15 at 21:08
What does this mean? "It will not allow Table2.Code to be repeated in Table3, since it is a unique ID..." — shawnt00, Aug 06 '15 at 21:10

score 2 · Answer 1 · answered Aug 06 '15 at 20:48

2

This will help you locate the problem:

SELECT ID, Code
FROM Table1
CROSS JOIN Table2
WHERE Some_conditions...
GROUP BY ID, Code
HAVING COUNT(*) > 1

answered Aug 06 '15 at 20:48

shawnt00

16,443
3
17
22

^ Unfortunately, no rows were returned, meaning there are no duplicates (as I suspected). – Dean Zaslow Aug 06 '15 at 20:51

score 1 · Answer 2 · answered Aug 06 '15 at 20:50

1

I presume that the reason you are getting this error is because table 2 has multiple rows of the same code for the same ID.

For example, table 2 might have two or more rows of ID 1024 and code 'PSV'.

A simple solution to fix this would be to modify your code as follows:

INSERT INTO Table3
SELECT DISTINCT ID, Code
FROM Table1
CROSS JOIN Table2
WHERE Some_conditions...

answered Aug 06 '15 at 20:50

Russ

4,091
21
32

^ I have tried this to no avail as well - There are NO duplicates of both records. IDs and Codes are both repeated, but never together. 1024 PSV, 1027 PSV, and 1024 RFT should represent 3 different PKs. – Dean Zaslow Aug 06 '15 at 20:54

score 0 · Answer 3 · answered Aug 06 '15 at 21:25

0

SQL Server had created a unique, non-clustered index for Table3 that was preventing the INSERT INTO statement from executing. I disabled it with SQL Server Management Studio Object Explorer and it allowed me to enter the rows.

answered Aug 06 '15 at 21:25

Dean Zaslow

87
2
9

INSERT INTO SELECT CROSS JOIN Composite Primary Key

3 Answers3