471,092 Members | 1,542 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

Fast copy big table content

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.

Jul 23 '05 #1
2 9391
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

Jul 23 '05 #2

"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.

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Liming | last post: by
4 posts views Thread by Alexis Gallagher | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.