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!
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:
How to do it directly with just 3 T-SQL commands? Easy...
Step 1 - declare a table variable @name
Step 2 - insert the file list into the table variable @name
* Note that the command xp_DirTree need as first parameter the window folder
Step 3 - Join out product table with the @name table
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?
I feel SQL and other related aspects truly help out people in finding how useful and vital are the operations.
ReplyDeleteSQL Server Load Rest API