SQL Server, How to Read a list of files in a folder and compare its name with the value stored into a columns

Hello friends and welcome back!

Today, first of all I would like to say a big thank you to all of you!

We have reached 4000 friends! 😀😀😀

(…I don't like to call "followers”)

Today we will talk about a practical case: how to compare the names of files contained in a folder with the same ones indicated in a field of a database

Let’s go!

What is the problem we want to solve today?

Suppose having a list of files in a window folder.
If you customer would ask you to see where a filename read from the image column existing on the filesystem?

So we have a window image folder:

 

We have also a table with a column image.
In the image column we have the path of our images!

How to do it directly with just 3 T-SQL commands? Easy...

Step 1  - declare a table variable @name

       

DECLARE @name TABLE (FileName nvarchar(500),depth int,isFile int)
       
 

Step 2 - insert the file list into the table variable @name

 

       
      
INSERT INTO @name
EXEC XP_DIRTREE '\\Srvsql01\PRODUCT_IMAGES' ,  1 , 1      
       
 

 

* Note that the command xp_DirTree need as first parameter the window folder


Step 3  - Join out product table with the @name table

 

 

       

SELECT Codice, Descr, image 
FROM Products p
WHERE
  NOT EXISTS (SELECT FileName
              FROM @name t 
              WHERE '\\Srvsql01\PRODUCT_IMAGES\'+t.FileName = p.image  ) 

 

 

That's all for Today!
Luca

Previous post: SQL Server: How to get the Last Inserted Identity value. What is the fastest way?