What is the fastest way to tranfer table between 2 SQL 7server

[balage0]

Dear Listmembers,

I have to arhive 3 million record into a single CD from 29 table month by month where the records older then 3 months.
My original plan was :
1 create new table as part of a select
2 drop old records
3 transfer this tables to a flat txt file

Now: I have created the archieve tables, and transferred into an admindb.
But now I realise that to copy 100,000 record to an other db.tables, then it is longer than 10 minutes.
The DTS looks like slow to do this staff to me.

I wanted to store it an other database that I can backup later (the full db) .

The question is do you have any idea how can I transfer rows faster than this?

Many thanks forward.

Cheers
Balage

"there are no planned mistakes, just unplanned success"

 

[nigelrivett]

Try using bcp.
This will be faster for crating the flat file.

It should also be faster to transfer to another table by bcp out to a file then bcp in. Especially if you use fast bcp and native file format.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[balage0]

Thanks for your information, I'll try bcp.

Cheers,
B