Need Quick Help on a SQL Script Addition 1

[grippy]

ok here is the code I have currently

UPDATE    tblProduct
SET              retailPrice = tblProductPriceUpdate.retailPrice, jobberPrice = tblProductPriceUpdate.jobberPrice
FROM         tblProductPriceUpdate
WHERE     tblProductPriceUpdate.company = tblProduct.company AND tblProductPriceUpdate.productCode = tblProduct.productCode

Basically it finds all the Product Codes for a Company and updates the price structures. This works fine but what I need is to send all of the product codes from tblProductPriceUpdate that could not be found in tblProduct and have product codes and prices sent to tblProductPriceNotUpdate. So this way I know if there are some new products that need to be added to the database. Let me know what you come up with. Thank you.

 

[AlexCuse]

Why not just run a select query like this:

select * from tblProductPriceUpdate
left join tblProduct
on tblProductPriceupdate.productCode = tblProduct.productCode
where tblProduct.productCode is null

This should tell you what you need to know.

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[grippy]

I ran that script and it seems to work except i need to be able to export this data so I need to go into tblProductPriceNotUpdate. Also on that script when it returns results it shows extra fields. It has productCode, company, jobberPrice, retailPrice, mapPrice (all filled in properly) then after those it goes to productCode, company, salePrice, retailPrice, costPrice, jobberPrice, mapPrice (which all show NULL). What are those extra fields needed for? Let me know the easiest way to export these results. Thank you. Im fairly new with SQL Server so bare with me.

 

[AlexCuse]

all you need to do is replace * with the fields you want to include. Make sure to do it like this:

tblProductPriceUpdate.YourField

as you are referencing > 1 table

If you need to export, I would do it via dts, but bcp might also be worth looking into depending on your situation.

A wise man once said
"The only thing normal about database guys is their tables".

 

[grippy]

ok got it to get rid of those extra fields. Here's the final code:

SELECT     dbo.tblProductPriceUpdate.productCode, dbo.tblProductPriceUpdate.company, dbo.tblProductPriceUpdate.jobberPrice, 
                      dbo.tblProductPriceUpdate.retailPrice, dbo.tblProductPriceUpdate.mapPrice
FROM         dbo.tblProductPriceUpdate LEFT OUTER JOIN
                      dbo.tblProduct ON dbo.tblProductPriceUpdate.productCode = dbo.tblProduct.productCode
WHERE     (dbo.tblProduct.productCode IS NULL)

Can you change it to where it puts the results in tblProductPriceNotUpdate which has all the exact same field names and data types? Once the script does that then I know how to do a DTS package from there. Let me know. Thanks

 

[AlexCuse]

how about this?

--clear out data from last time via one of these options
--truncate table tblProductPriceNotUpdate
--delete from tblProductPriceNotUpdate 

--insert data for this time
insert into tblProductPriceNotUpdate (productCode, company, jobberPrice, retailPrice, mapPrice)
SELECT     dbo.tblProductPriceUpdate.productCode, dbo.tblProductPriceUpdate.company, dbo.tblProductPriceUpdate.jobberPrice, 
dbo.tblProductPriceUpdate.retailPrice, dbo.tblProductPriceUpdate.mapPrice
FROM         dbo.tblProductPriceUpdate LEFT OUTER JOIN
                      dbo.tblProduct ON dbo.tblProductPriceUpdate.productCode = dbo.tblProduct.productCode
WHERE     (dbo.tblProduct.productCode IS NULL)

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[grippy]

worked perfectly! thanks I have it all exported to my excel doc and printed out. Youve been a great help and saved me hours of time. Thank you.