Get files in different folder using sql

[ntuyen01@yahoo.com]

Hi All,

I have a multiple files, but they are store in different directory on
the server. I want open those files and insert it into the database
using bcp.

Example files structure dir:
\\xyz\123\abc\t ext1.txt
\\xyz\123\abc\t ext2.txt
\\zyz\123\999\t ext2.txt

bcp "dabase" in \\xyz\123\abc\t ext1.txt -c -S"servername ' -Usa
-Ppassword -T".

is there away to loop througth each dir, get the files, excecute the
bcp, then go to next folder.

Please help. Thanks in advance.

Ted Lee

[ntuyen01@yahoo.com]

Re: Get files in different folder using sql

I get it. Never mind.


ntuyen01@yahoo. com wrote:

Hi All,
>
I have a multiple files, but they are store in different directory on
the server. I want open those files and insert it into the database
using bcp.
>
Example files structure dir:
\\xyz\123\abc\t ext1.txt
\\xyz\123\abc\t ext2.txt
\\zyz\123\999\t ext2.txt
>
bcp "dabase" in \\xyz\123\abc\t ext1.txt -c -S"servername ' -Usa
-Ppassword -T".
>
is there away to loop througth each dir, get the files, excecute the
bcp, then go to next folder.
>
Please help. Thanks in advance.
>
Ted Lee

[Erland Sommarskog]

Re: Get files in different folder using sql

(ntuyen01@yahoo .com) writes:

I have a multiple files, but they are store in different directory on
the server. I want open those files and insert it into the database
using bcp.
>
Example files structure dir:
\\xyz\123\abc\t ext1.txt
\\xyz\123\abc\t ext2.txt
\\zyz\123\999\t ext2.txt
>
bcp "dabase" in \\xyz\123\abc\t ext1.txt -c -S"servername ' -Usa
-Ppassword -T".
>
is there away to loop througth each dir, get the files, excecute the
bcp, then go to next folder.

You could do

INSERT #tmp(files)
EXEC master..xp_cmds hell 'DIR /B \\xyz\123\abc\* .tmp'

Then run a cursor of #tmp.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx