1

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 .

Mostafa
  • 3,002
  • 10
  • 52
  • 79

2 Answers2

3

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
Jon
  • 3,230
  • 1
  • 16
  • 28
1

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.

http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/

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.

Chris Gessler
  • 22,727
  • 7
  • 57
  • 83