Import data from multiple tables into one table

[jmj]

I need to import data from 25 dbIV tables. I only need 6 fields from each table. All the info from the 25 tables will be consolidated into 1 sql table. I will need to turn it into a job so that the data is refreshed on a regular basis.
What I would like to do is connect to the db4 database and do a query to only append the 6 needed fields from each of the tables.
I'm not sure how to do this- is it possible with dts? dts from what I've seen only allow one query per import and I don't want to have to have 25 packages for this.
The only other option I see is to import all 25 tables and run sql code against it to pull in the data I want. However, I don't like that idea much as these are big tables.
Any ideas about how to streamline this proccess?
thanks in advance for any ideas.
J

 

[DrSql]

Here one way to approch this.
Create db called dbname_temp
Then import all tables into the new db as one package.
Then create a view in dbname_temp with six fileds that you are planning to import.
Then do an import from that viewe to your prod dbname.

One thing you need to make sure if you are running on a regular basis trunacte all tables.

select 'truncate table '+name+';' from sysobjects
where xtype='U'


Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[jmj]

I do like the idea of having a temp db for these types of tables instead of cluttering up the actual db.
But I guess I still have to import all of the tables into the temp db.
Is there a way for all 25 imports to dump into 1 table or do I need to have 25 tables and then run append queries to consolidate the data? (these 25 tables are exactly the same fields/structure/etc- just has different data- like customer data from 25 different companies being dumped into one big customer table)
Can I set the truncate table statement in a job? I want this whole process automated.
Truncate table/import table
thanks,
j

 

[DrSql]

Can you send me a sample DB file so I can test before I direct you toa path.

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.