Importing multiple files to SQL

[chudson007@hotmail.com]

I have over three hundred text files that I need to import to SQL
Server.
Each is in the exact same format.
I want to import tham as seperate tables.
Is there any way to do it in one process?

Regards,
Ciarán

[David Portas]

Re: Importing multiple files to SQL

You can use DTS:

http://www.sqldts.com/default.aspx?246

If all the files are the same format then why not import them to a
single table with an extra column to identify the source file? That
should be much more convenient than creating 300 separate tables.

--
David Portas
SQL Server MVP
--

[shumaker@cs.fsu.edu]

Re: Importing multiple files to SQL

I use the above method, but if you want seperate tables:

You could add in dynamic properties and some scripts to the workflow of
the DTS. A script task sets a source filename dynamic property. A
create table task uses the dynamic property as the table name. Then
import into the new table from the file(again using the dynamic
properties to set the source filename and destination table name).

Using only one table will make some things much easier, and other
things harder. Consider that with multiple tables you will have to
either have a copy of all your queries for each table, or have a stored
procedure that allows the table name to be specified as a parameter. I
think it would be easier to put all the data in one table and have a
sproc that takes the code for the special column as one of the
filtering criteria. I think the syntax for specifying the criteria for
that column is no harder than specifying a table name.

[chudson007@hotmail.com]

Re: Importing multiple files to SQL

Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from

http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\Ciaran Hudson\My Documents and all its sub
directories?

[chudson007@hotmail.com]

Re: Importing multiple files to SQL

Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from

http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\Ciaran Hudson\My Documents and all its sub
directories?

[chudson007@hotmail.com]

Re: Importing multiple files to SQL

Ok, I've copied SQLDTS.com Loop Import and Archive (246).dts from

http://www.sqldts.com/default.aspx?246

I don't really understand the intricacies of how it works, but
presume that I need to change the source folders it looks at.
How do I get it to import all the .txt files in say
D:\Documents and Settings\Ciaran Hudson\My Documents and all its sub
directories?