Solved

Iterate through a collection of files in T-SQL

Posted on 2014-01-04
4
496 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now