How to loop through and get sub directories in a directory in SQL Server?

I am using Sql Server 2008. I can load the files in a directory; usning following code:

Set @Path = 'C:\Test\'
Set @param = 'dir /B ' + @Path + '*.txt'

What I want is to get the the files that are in the subdirectories of the Test directory. s开发者_如何转开发ay Test/temp and the temp folder contains multiple folders say 1,2,3(each contining multiple files). I want to loop through all the folders in the temp and get the files in each of its subdirectories(1,2,3). Then load the files in Sql tables.

Kindly advice

The best answer to this question is probably to not use SQL at all.

If you want to do something with subdirs you can experiment with tree /F, but as you will see that is harder to actually parse.

Perhaps you can try and look into a scripting language such as python or vbscript to help you out. You could scan the file, and perhaps even get more information about the files to put in your DB, like file size and last modified dates?

Use /S switch on dir to get content in subdirectories.