CODEWITHSUNDEEP
sql
Dean
Dean

Reputation: 159

SQL script executing but results not being shown

I am trying to bulk map products to a catergoryID table. Each product has a SKU code and then is mapped to a CategoryID table. The script executes fine but when I look the products are not mapped. Any idea where I maybe going wrong? Is it because the prodcutID already exists?

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 ('CLFLNW1',1252)
insert into tmp_products (SKU, CategoryID) values ('CLFROCR013',1252)
insert into tmp_products (SKU, CategoryID) values ('GBLGOKOM',1252)
insert into tmp_products (SKU, CategoryID) values ('HS008714',1252)
insert into tmp_products (SKU, CategoryID) values ('HS014928',1252)
insert into tmp_products (SKU, CategoryID) values ('HS400085',1252)
insert into tmp_products (SKU, CategoryID) values ('HS400093',1252)
insert into tmp_products (SKU, CategoryID) values ('HS400101',1252)
insert into tmp_products (SKU, CategoryID) values ('HS400135',1252)


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

Upvotes: 0

Views: 40

Answers (1)

Ruud Helderman
Ruud Helderman

Reputation: 11018

Your INSERT fails to add additional categories; once a product has one category in table ProductCategory, no more will be added.

You can add a WHERE condition to your subquery:

WHERE pc.CategoryID = tmp_products.CategoryID

The complete INSERT statement now becomes:

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
    WHERE pc.CategoryID = tmp_products.CategoryID
)
AND ProductID IS NOT NULL

Upvotes: 1

Related Questions