sql insert error

Question

This is my Insert Statement

INSERT INTO ProductStore (ProductID, StoreID, CreatedOn)
(SELECT DISTINCT(ProductId), 1, GETDATE() FROM ProductCategory
WHERE EXISTS (SELECT StoreID, EntityID FROM EntityStore
WHERE EntityType = 'Category' AND ProductCategory.CategoryID = EntityStore.EntityID AND StoreID = 1))

I am trying to Insert into table ProductStore, all the Products Which are mapped to Categories that are mapped to Store 1. Column StoreID can definitely have more than one row with the same entry. And I am getting the following error: Violation of Primary Key Constraint...

However, the Following query does work:

    INSERT INTO ProductStore (ProductID, StoreID, CreatedOn)
VALUES (2293,1,GETDATE()),(2294,1,GETDATE())

So apparently, the ProductID Column is trying to insert the same one more than once.

Can you see anything wrong with my query?

TIA

Answer 1

select distinct productid is selecting an existing ID and therefor in violation with your primary key constraint. Why don't you create the primary key using Identity increment? In that case you don't need to worry about the ID itself, it will be generated for you.

Answer 2

Take out the INSERT INTO statement and just run the SELECT - you should be able to spot pretty quickly where the duplicates are.

My guess is that you're slightly mistaken about what SELECT DISTINCT actually does, as evidenced by the fact that you have parentheses around the ProductId. SELECT DISTINCT only guarantees the elimination of duplicates when all columns in the select list are the same. It won't guarantee in this case that you only get one row for each ProductId.

Answer 3

I don't see any part of that query that excludes records already in the table.