I have thousands of photos in my hard-disk. All photo file-names consists of 4 parameter :
'7 digit number ' + '-' + '3 digit number ' + '.gif'
for instance : 1000091-356.gif
I'm wondering how to write a query in order to use 3 digit number of file name as a paramer for updating my table which primary key match the 7 digit number of the file name.
By another word a query which act like : update myTable set col2 = 356 where col1=1000091 for all photos .
Try getting your file names into a table first, (using the script below)
CREATE TABLE dirList (
id int identity(1,1),
line nvarchar(1000)
)
GO;
INSERT INTO dirList (line) EXEC xp_cmdshell 'dir C:\PathToMyPhotos'
SELECT * FROM dirList;
WITH CTE AS (
SELECT
id,
SUBSTRING(line,1,17) [date],
SUBSTRING(line,18,19) sizeordir,
SUBSTRING(line,37,100) name
FROM dirList
WHERE id > (
SELECT MIN(id) FROM dirList WHERE line LIKE '%<DIR>%..%'
) AND id < (SELECT MAX(id) - 2 FROM dirList)
)
SELECT
id,
[date],
isDirectory = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 1 ELSE 0 END,
isFile = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 0 ELSE 1 END,
name
FROM cte
Now you can query your filenames using:
declare @Lookup varchar(3)
set @Lookup = '123'
select name
from dirList
where isFile = 1 and substring(name, len(name)-7,3) = @Lookup
You'll have to tap into the power of sp_OACreate and the FileSystemObject in order to read filenames from the local machine.
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
Here's an article that explains this in full detail.
If all you really need is the filenames, FSO might be too powerful for what you need. I almost forgot about xp_cmdshell, which is perfect for a little job like "DIR" - see Mangist's answer.
xp_cmdshell would be much simpler.