to import multiple text files into a database

[cooperchien]

I will have 50 to 70 text files of data that I need to import into a SQL database every week from now. I understand that I may use DTS to import the text files, but I have to do it 50 to 70 times.......Is there any quicker way for me to do this job?
The text files all have the format that SQL server understands.

 

[mrdenny]

Are the files all in the same format?

There are three ways to load data from a text file into the SQL Server.

1. DTS
2. BCP (command line utility)
3. BULK INSERT (T/SQL Command)

Both BCP and Bulk Insert support format files in case the text file format isn't the easiest to work with.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[cooperchien]

the problem I face is that I have many text files. Do I have to import them one by one? I am wondering if there is a way I can make SQL get all the single files in one folder and import it.
Yes. All the text files are in the same format.

 

[mrdenny]

There isn't a way to have SQL do that per say. However you can use a batch file to call the BCP command for every file in the folder.

Put this in a batch file, then run the batch file.

@for /F %%i in (`dir /b c:\folder\`) do  bcp "Database.dbo.Table" IN "%%i" -S ServerName -T -w -b 1000

You may need extra switches for the bcp command.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com