I have a folder full of date-stamped files: 010101.txt, 010201.txt,
010301.txt, etc.

I have a working bcp command that points to a specific file.

declare @bcpCommand varchar(1000)
select @bcpCommand = 'BCP "Import.dbo.NAV" in
"d:\folder\010101.txt" -c -T -t, -S "SERVER1\SERVER1"'
EXEC master.dbo.xp_cmdshell @bcpCommand

How can I script the bcp command so it will import all files between say
010101 and 120105? Complicating factor; there are gaps in the sequence. Can
the script continue even if it doesn't find a file?

Alternatively, if it was possible to issue the bcp command against all files
in a folder no matter what the filename, that would work also.

This is a one-time data load with SQL Server 2000/SP4

Thanks to anyone who could help.

For sake of simplicity, write a cursor script & get it off. Here is a quick
try:

DECLARE @cmd VARCHAR(500), @c VARCHAR(500)
CREATE TABLE #t ( c VARCHAR( 500 ) NOT NULL )
INSERT #t EXEC master.dbo.xp_cmdshell 'DIR d:\folder\*.txt /B'
DECLARE c CURSOR FOR SELECT f FROM #t
OPEN c
FETCH NEXT FROM c INTO @c
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = 'BCP "Import.dbo.NAV" in "d:\folder\' + @c + '" -c -T -t, -S
"SERVER1\SERVER1"'
   EXEC master.dbo.xp_cmdshell @cmd
   FETCH NEXT FROM c
END
CLOSE c
DEALLOCATE c
DROP TABLE #t ;

convert flat file to native file format for bcp
Disappearing data (happens rarely)
Query Help: Counts & Sums
Year To Date?
trying to do some weird math in an update statement