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.
