Solved

Iterate through a collection of files in T-SQL

Posted on 2014-01-04
4
498 Views
Last Modified: 2014-01-04
I want to iterate through all the jpeg files in a folder, inserting them into a filestream column in SQL Server. I have a stored procedure to insert the files, but I don't know how to read through a series of folders and files using T-SQL.
0
Comment
Question by:TimHudspith
  • 2
4 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 39755855
you can achive this by using xp_cmdshell commands and bulk copy.
0
 

Author Comment

by:TimHudspith
ID: 39755860
Can you give me an example?
0
 
LVL 26

Expert Comment

by:tigin44
ID: 39755897
0
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 500 total points
ID: 39755911
You can try the following code:

IF OBJECT_ID('tempdb..#Files') IS NOT NULL DROP TABLE #Files;

CREATE TABLE #Files
    (
      ID INT IDENTITY(1, 1) ,
      Subdirectory NVARCHAR(512) ,
      Depth INT ,
      IsFile BIT
    );

INSERT  #Files( Subdirectory, Depth, IsFile )
EXEC master.sys.xp_dirtree 'C:\', 1, 1;

DECLARE @ID INT        
DECLARE FileCursor CURSOR FAST_FORWARD
FOR
    SELECT  ID
    FROM    #Files
    WHERE   IsFile = 1
    ORDER BY ID

OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @ID

WHILE @@FETCH_STATUS = 0 
    BEGIN   
        DECLARE @FileName VARCHAR(256)
        SELECT  @FileName = Subdirectory
        FROM    #Files
        WHERE   ID = @ID

        PRINT @FileName

	FETCH NEXT FROM FileCursor INTO @ID;
    END
CLOSE FileCursor;
DEALLOCATE FileCursor;

Open in new window


instead of print @filename write your own code for each iteration.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question