SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Primary Key Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deanglen
Yak Posting Veteran

64 Posts

Posted - 01/23/2013 :  06:11:55  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/23/2013 :  07:04:59  Show Profile  Reply with Quote
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

64 Posts

Posted - 01/23/2013 :  07:12:49  Show Profile  Reply with Quote
Thanks. But still getting the same error.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/23/2013 :  07:14:58  Show Profile  Reply with Quote
whats the primary key in ProductCategory table?

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

Go to Top of Page

deanglen
Yak Posting Veteran

64 Posts

Posted - 01/23/2013 :  07:18:13  Show Profile  Reply with Quote
1.CategoryID
2.ProductID
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/23/2013 :  07:21:10  Show Profile  Reply with Quote
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

64 Posts

Posted - 01/23/2013 :  07:23:58  Show Profile  Reply with Quote
Seems to be working! Thanks!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/23/2013 :  10:47:13  Show Profile  Reply with Quote
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

64 Posts

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

deanglen
Yak Posting Veteran

64 Posts

Posted - 01/23/2013 :  12:29:50  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/23/2013 :  13:43:26  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000