Quick Help on SQL Script

[grippy]

Hello all, Im currently using the following script:

CREATE TABLE #files(filename varchar(255)) INSERT INTO #files EXEC master..xp_cmdshell 'dir C:\Inetpub\[URL unfurl="true"]wwwroot\company\html\images\productSpecific\medium1\*.jpg[/URL] /b'
                                                                                                                            UPDATE    P
                                                                                                                             SET              [image] = 1
                                                                                                                             FROM         tblProduct P INNER JOIN
                                                                                                                                                    tblCompany C ON P.company = C.ID INNER JOIN
                                                                                                                                                    #files F ON F.filename = C.name + ' ' + CONVERT(varchar(6), P.productCode) + '.jpg'

This code will scan my product table and check each product to see if I have a specific image for it in a local folder. If I do have an image it updates my database and sets the field "image" = 1. It works great except it does not work for products that have dashes in them. Heres a sample product code:

T56-89GH and the actual image file would be named: CompanyName t56 89gh.jpg

My images are all named without dashes and just have spaces in place of the dash but the actual product code in the database still maintains the dash. Let me know if you guys know how to make this work. Thank you.

 

[markros]

In your JOIN condition change

ON F.filename = C.name + ' ' + CONVERT(varchar(6), P.productCode) + '.jpg'

to

ON F.filename = C.name + ' ' + CONVERT(varchar(6), replace(P.productCode,'-',' ')) + '.jpg'

See also

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-extracting-data-again

 

[grippy]

yea i figured it was something else like a replace command but im not very good with the whole Join commands. Thanks a bunch for your help Ill try it out right now!

 

[grippy]

hmm just tried it and it did not seem to work Any other suggestions?

 

[markros]

Can you post some of the items in #F table and in the Products table?

Try for now

select F.*, P.Product_Code, C.Company from table_Products P inner join Company C on P.Company = C.ID inner join #Files F
on F.filename LIKE C.name + ' %' where P.Product_Code like '%-%'

and see how the files and names correspond to each other.

 

[grippy]

ok so i found out something strange...when I run the original code with your replace condition it says 7143 results changed but when I look up tblProduct.image there are only 420 of them set to 1.

When I try that last code you posted it says LIne 1: Incorrect syntax near 'F'.

tblFiles doesnt have any data in it so I cant post anything
tblProduct here are some of the productCode that I know for sure have images but just wont take:

57-2505-1
63-1100
57-3026
57-3013-2

Let me know if there is any other info you need. TY

 

[markros]

I'm sorry, I see nothing wrong in this code I posted except that I didn't use your precise table names, but I hope you can adjust for that.

I'm not sure why it's not working - try for now to make selects instead of UPDATE to see what the problem may be.