Hi,
Try this code..
I created a copy of authors table in the pubs database called NewAuhors and then created 2 Tab delimited text files with data from the NewAuhors table using DTS Import/Export Wizard,and saved them in C:\Import directory, and then deleted all data in NewAuhors Table, and executed the code below.. All data from 2 text files r imported back into the table after execution of the code.
DELETE FROM NEWAuhors
drop table #t
create table #t(o varchar(8000))
insert into #T
EXEC master..xp_cmdshell 'dir c:\Import\*.txt' -- GET ALL Txt file from the directory
delete from #t where (o not like '%.txt' or o is NULL) -- Remove all Unwanted rows
Declare @filename varchar(100)
Declare @SQL varchar(5000)
Declare fileCursor Cursor for select ltrim(reverse(Substring(reverse(o),1,Charindex(' ',reverse(o))))) from #T
OPEN fileCursor -- Open Cursor to get all the file names from the directory
FETCH NEXT FROM fileCursor Into @filename
While @@fetch_status = 0
Begin
SET @SQL = 'BULK INSERT pubs.dbo.[NewAuhors]
FROM ''c:\import\' + @filename + ''' WITH ( FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''\n'')'
--print @SQL
exec(@SQL) -- Insert into the Table
FETCH NEXT FROM fileCursor Into @filename
End
CLose fileCursor
Deallocate fileCursor
select * from NewAuhors -- Check to see whether values r entered or not
This is the Sample Text file I used to import data, file name was: BatchRegistration01012003.txt
172-32-1176 White Johnson 408 496-7223 10932 Bigge Rd. Menlo Park CA 94025
213-46-8915 Green Marjorie 415 986-7020 309 63rd St. #411 Oakland CA 94618
Note: One assumption I am making in this is that file names dont have space in them for eg: "batch registration 01022003.txt"
I hope this is what u r looking for...
Sunil
