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.

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

"New MSSQL DBA" <boscong88@gmail.com> wrote in message
news:1119515833.641071.147380@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.
>