Need to Create File Listing through T-SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need to Create File Listing through T-SQL

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2011-06-17 : 01:13:33

Hello all,

I am having a problem:
I have two tables:

MAIN_TABLE
---------------------------------------------------
ID | Title | Link
---------------------------------------------------
 1 | air11 | \\server1\FILE_MANAGEMENT\air\air11
 2 | air12 | \\server1\FILE_MANAGEMENT\air\air12
 3 | air13 | \\server1\FILE_MANAGEMENT\air\air13
---------------------------------------------------
SUB_TABLE
--------------------------
ID | TitleID | FileName
--------------------------
 1 |   1     |  a1.dwg
 2 |   1     |  a2.dwg
 3 |   2     |  a5.dwg
 4 |   3     |  a3.dwg
--------------------------

Now let me explain my problem, in the MAIN_TABLE, whenever I'll to insert TITLE & LINK, a procedure or a trigger should run to fill up the SUB_TABLE with the list of files provided in the LINK column of MAIN_TABLE. The LINK column basically contains the path of a directory, where there contains multiple files.
I came to know how to generate directory listing from the following link:
[url]http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-create-directory.html[/url]
But unable to understand to get file listing.

Hope I can make you understand my query!
Can you please help me on this?

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-17 : 07:09:11

To get files
master..xp_cmdshell 'dir /B filepath'
or see
http://www.nigelrivett.net/SQLTsql/CheckIfFileExists.html

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

daipayan
Posting Yak Master

181 Posts

Posted - 2011-06-20 : 01:52:45

Thank You nigelrivett

quote:
Originally posted by nigelrivett

To get files
master..xp_cmdshell 'dir /B filepath'
or see
http://www.nigelrivett.net/SQLTsql/CheckIfFileExists.html

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources