Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Primary Key Error

Author  Topic 

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-01-23 : 06:11:55
Hi

I am using SQL to map products that do not exist in a category table to a category ID. Trying to run a thousand at a time, here is an example of the query using one Product

create table tmp_products (ProductID int, SKU nvarchar(100) collate SQL_Latin1_General_CP1_CI_AS, CategoryID int)

insert into tmp_products (SKU, CategoryID) values ('VGA1686',827)

update tmp_products
set ProductID = p.ProductID
from product p
join tmp_products t on t.SKU = p.SKU

insert into ProductCategory (ProductID, CategoryID, DisplayOrder)
select ProductID, CategoryID, 1
from tmp_products
where ProductID not in
(select pc.ProductID
from ProductCategory pc
join tmp_products tp on tp.ProductID = pc.ProductID)
and ProductID is not null

drop table tmp_products

However when I execute this for a thousand products I am getting this error

Msg 2627, Level 14, State 1, Line 522
Violation of PRIMARY KEY constraint 'PK_ProductCategory'. Cannot insert duplicate key in object 'dbo.ProductCategory'.
The statement has been terminated.

None of the products that I run the script for are being mapped because one product already is mapped to the Category ID. Is there anyway to use a command to override this if the products that is causing the issue already exists?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 07:04:59
make insert like

insert into ProductCategory (ProductID, CategoryID, DisplayOrder)
select ProductID, CategoryID, 1
from tmp_products t
where not exists
(select 1
from ProductCategory pc
WHERE ProductID = t.ProductID
AND CategoryID = t.CategoryID)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-01-23 : 07:12:49
Thanks. But still getting the same error.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 07:14:58
whats the primary key in ProductCategory table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-01-23 : 07:18:13
1.CategoryID
2.ProductID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 07:21:10
wat about this?

insert into ProductCategory (ProductID, CategoryID, DisplayOrder)
select distinct ProductID, CategoryID, 1
from tmp_products t
where not exists
(select 1
from ProductCategory pc
WHERE ProductID = t.ProductID
AND CategoryID = t.CategoryID)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-01-23 : 07:23:58
Seems to be working! Thanks!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 10:47:13
that means you have duplicates existing in table tmp_products for combination.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-01-23 : 11:22:49
But would not the table be closed after the SQL script is executed?
Go to Top of Page

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-01-23 : 12:29:50
This is no longer working I am now getting this error

Msg 515, Level 16, State 2, Line 509
Cannot insert the value NULL into column 'ProductID', table 'multistorenc.dbo.ProductCategory'; column does not allow nulls. INSERT fails.
The statement has been terminated.

I am really no idea what is happening can anyone help?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 13:43:26
you've some record in tmp_products that has NULL value for ProductID
Try


insert into ProductCategory (ProductID, CategoryID, DisplayOrder)
select distinct ProductID, CategoryID, 1
from tmp_products t
where not exists
(select 1
from ProductCategory pc
WHERE ProductID = t.ProductID
AND CategoryID = t.CategoryID)
AND ProductID IS NOT NULL


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -