Get files in different folder using sql

[ntuyen01]

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\text1.txt
\\xyz\123\abc\text2.txt
\\zyz\123\999\text2.txt

bcp "dabase" in \\xyz\123\abc\text1.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]

I get it. Never mind.
nt******@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\text1.txt
\\xyz\123\abc\text2.txt
\\zyz\123\999\text2.txt

bcp "dabase" in \\xyz\123\abc\text1.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]

(nt******@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\text1.txt
\\xyz\123\abc\text2.txt
\\zyz\123\999\text2.txt

bcp "dabase" in \\xyz\123\abc\text1.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_cmdshell 'DIR /B \\xyz\123\abc\*.tmp'

Then run a cursor of #tmp.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx