| Author |
 Topic  |
|
|
deanglen
Starting Member
40 Posts |
 Posted - 01/23/2013 : 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
India
47023 Posts |
Posted - 01/23/2013 : 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/
|
 |
|
|
deanglen
Starting Member
40 Posts |
Posted - 01/23/2013 : 07:12:49
|
| Thanks. But still getting the same error. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/23/2013 : 07:14:58
|
whats the primary key in ProductCategory table?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
deanglen
Starting Member
40 Posts |
Posted - 01/23/2013 : 07:18:13
|
1.CategoryID
2.ProductID |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/23/2013 : 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/
|
|
|
|
deanglen
Starting Member
40 Posts |
Posted - 01/23/2013 : 07:23:58
|
| Seems to be working! Thanks!!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/23/2013 : 10:47:13
|
that means you have duplicates existing in table tmp_products for combination.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
deanglen
Starting Member
40 Posts |
Posted - 01/23/2013 : 11:22:49
|
| But would not the table be closed after the SQL script is executed? |
|
|
|
deanglen
Starting Member
40 Posts |
Posted - 01/23/2013 : 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? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/23/2013 : 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/
|
|
|
| |
Topic |
|
Primary Key Error
