Script executes ok but no results

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

Script executes ok but no results

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2014-04-02 : 11:13:12

Hi

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?

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-02 : 11:42:09

For testing, change the UPDATE statement to a SELECT statement and see if you get any rows from the JOIN

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

deanglen
Yak Posting Veteran

65 Posts

Posted - 2014-04-02 : 11:45:54

Seems to show results in SQL Management Studio

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-02 : 15:00:08

Is the issue that tmp_products is not being updated, or is it that the data is not getting inserted into the ProductCategory table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources