Using DTS to fill multiple tables from one .txt

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using DTS to fill multiple tables from one .txt

Author

Topic

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-08-09 : 16:55:23

I have one text file that I need to import into my SQL database. It contains all the data I need

Is it possible to use DTS to import the data (from the text file) into 6 different tables within 1 SQL database? Thanks

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-09 : 17:02:40

it sure it. But this is how I would do it. I would get the data into one temp table in the database and then from that table i will select the required columns in the following 6 tables.

I do this a lot. And this is how I do it. Select the text file as source and create a table which will have all the columns in the text file. That makes it lot simpler.

For me I don't have much data to import. Its about 100 rows every 5 minutes so the package is excecuted in minutes. How many rows at a time are we talking about here. And how often

Ashley Rhodes

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-08-09 : 17:14:25

Not much data here either. Maybe 400 rows at a time, only once a day.

So if I go with your recommended way, I dump the entire text file into one table. From there, how would I select which fields I would like to be copied and moved? DTS?

Thanks, Im a newb.

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-10 : 12:07:05

400 rows once a day is not much of a big deal
say you create table to get all data from the text file

create table Main_Table
(
C1 varchar(20),
C2 varchar(10),
C3 float,
C4 int,
C5 char(2),
C6 int
)

you have these 6 columns in the table from the text file.

Now you have a table T2 which has columns C1, C3 and C5
Table T3 has columns C2, C3, C4
Table T4 has columns C3, C6

then you insert into T2, T3 and T4 from Main_Table

insert into T2 select C1, C2, C5 from Main_table
where C5 = condition_if_apply

insert into T3 select C2, C3, C4 from Main_Table

insert into T4 select C3, C6 from Main_Table

make sure you have all the primay keys in place.

Before you do the inserts next days make sure you truncate the main table. This way the table will not grow very big so you will not need to drop it and create it everytime.

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources