DTS PERFORMANCE

[jrc1]

I am exporting data from one dB/table to another db/table on the same server using DTS. I specify the source and destination and enter a query to pull specific data. After entering the query, DTS freezes for approximately 15-20 minutes before moving to the next screen. Each of the databases is approximately 9 gig.

1. Is this a normal time span for DTS?
2. Is there any other way to move specific data from one table to another? The tables are very simialar tables(not exact) in different databases on the same server.

Jeff
jchie@vafb.com

 

[aalmeida]

I think you would be better off using a direct query like this:

Select * from DB1.dbo.TBL1
where Db1.dbo.tbl1.col2 = Db2.dbo.Tbl2.Col2

You can also join tables in two Db as well AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[dbmsguy]

you could:

1) do it with dts as you are doing.
2) do it with an inser stmt. non matching columns would have to be fixed in the insert. as well, the insert would be one big transaction so you would have transaction log issues to deal with if the table is large
3) do 2, but break it up by some logical data limiation> ie put in a where clause
4) do it with bcp out and them bcp in. this could be put in a dts package. parameters could handle the transaction log issues if any.

those are the basic methods that come to mind. Paul

http://www.dbmsguy.com