Fast copy big table content

[New MSSQL DBA]

Hi all,

need advice on the following task:
copy the content of a big table from DB_A to DB_B in the same server

the size of table:
~ 7 million rows, ~ 9G in size, 1 clustered pk index, 13 nonclustered
index

current practice:
use DTS to copy the data, takes over 20 hours as
-- first had to delete existing data of the table in DB_B
-- then copy
-- all these happen while all indexes are in place.

I am trying to check what is the best or most efficient way to copy
this kind of data and what would
be the expected time for such load.

my machine: SQL 2000 Enterprise, 8-way P4, 12G RAM on a EMC Clarrion
600 SAN.

[DM Unseen]

Most efficient way is:

drop table on DB_B (remember that this invalidates inceremenal backups.

Allow bulk inserts, set recovery to simple or bulk insert on DB_B

use DB_B

then do a

select * FROM DB_A.table_source WITH(NOLOCK) INTO table_dest

Then

CREATE INDEXS
ALTER TABLE ADD PRIMARY KEY

There is no faster way;)

DM Unseen

[Greg D. Moore \(Strider\)]

"New MSSQL DBA" <bo*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi all,

need advice on the following task:
copy the content of a big table from DB_A to DB_B in the same server

the size of table:
~ 7 million rows, ~ 9G in size, 1 clustered pk index, 13 nonclustered
index

current practice:
use DTS to copy the data, takes over 20 hours as
-- first had to delete existing data of the table in DB_B
-- then copy
-- all these happen while all indexes are in place.
Dropthe indices and use bulk insert or BCP and then rebuild your indices.

I routinely load a table even bigger than that in under 6 hours this way.
(and I really haven't spent much time optimizing the process since we moved
to the new hardware, so I might be able to improve that further.)


I am trying to check what is the best or most efficient way to copy
this kind of data and what would
be the expected time for such load.

my machine: SQL 2000 Enterprise, 8-way P4, 12G RAM on a EMC Clarrion
600 SAN.