Author |
Topic |
deanglen
Yak Posting Veteran
65 Posts |
Posted - 2013-01-23 : 06:11:55
|
HiI 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 Productcreate 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, 1from tmp_productswhere ProductID not in (select pc.ProductID from ProductCategory pc join tmp_products tp on tp.ProductID = pc.ProductID)and ProductID is not nulldrop table tmp_productsHowever when I execute this for a thousand products I am getting this errorMsg 2627, Level 14, State 1, Line 522Violation 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 likeinsert into ProductCategory (ProductID, CategoryID, DisplayOrder)select ProductID, CategoryID, 1from tmp_products twhere not exists (select 1 from ProductCategory pc WHERE ProductID = t.ProductIDAND CategoryID = t.CategoryID) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
deanglen
Yak Posting Veteran
65 Posts |
Posted - 2013-01-23 : 07:12:49
|
Thanks. But still getting the same error. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
deanglen
Yak Posting Veteran
65 Posts |
Posted - 2013-01-23 : 07:18:13
|
1.CategoryID2.ProductID |
|
|
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, 1from tmp_products twhere not exists (select 1 from ProductCategory pc WHERE ProductID = t.ProductIDAND CategoryID = t.CategoryID) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
deanglen
Yak Posting Veteran
65 Posts |
Posted - 2013-01-23 : 07:23:58
|
Seems to be working! Thanks!!! |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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? |
|
|
deanglen
Yak Posting Veteran
65 Posts |
Posted - 2013-01-23 : 12:29:50
|
This is no longer working I am now getting this errorMsg 515, Level 16, State 2, Line 509Cannot 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
52326 Posts |
Posted - 2013-01-23 : 13:43:26
|
you've some record in tmp_products that has NULL value for ProductIDTryinsert into ProductCategory (ProductID, CategoryID, DisplayOrder)select distinct ProductID, CategoryID, 1from tmp_products twhere not exists (select 1 from ProductCategory pc WHERE ProductID = t.ProductIDAND CategoryID = t.CategoryID)AND ProductID IS NOT NULL ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|