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?

 

 

Comments

  1. I feel SQL and other related aspects truly help out people in finding how useful and vital are the operations.

    SQL Server Load Rest API

    ReplyDelete

Post a Comment

I Post più popolari

SQL Server, execution plan and the lazy spool (clearly explained)

SQL Server, datetime vs. datetime2

La clausola NOLOCK. Approfondiamo e facciamo chiarezza!