[dba-SQLServer] Moving tables back and forth between databases

John W. Colby jwcolby at colbyconsulting.com
Mon Nov 8 22:26:01 CST 2004


Can SQL Server EM copy tables from a database in one SQL Server (Neo2) to
another SQL Server (Neo1)?  I am trying to tear down the big 64 million
record 640 field table into sub-tables.  The process consists of creating
the tables, building an append query to copy the data from the big table to
the new sub-table, then creating indexes on every field in the sub-table.
The process of creating the new tables is relatively easy and quick,
manually copying the field defs into the paste buffer, then pasting them
back in to a new table.  The process of creating and executing the append
query is relatively painless and fairly quick though it does take a couple
of hours (I think, never actually timed it) to copy the data.  However the
process of creating the indexes is time consuming both in the doing
(manually in table design view) and particularly in the execution (saving
the table with the new indexes - which can take overnight.  The process of
creating the indexes pretty much locks up the machine (80-100% CPU
utilization) for many many hours.

I would like to create the sub-tables all at once, then farm out the index
creation to my other machines, but in order to do that I have to copy the
data filled tables to another machine, switch to that machine, create the
indexes in design view, then save the table in design view which starts the
index creation process happening.  

I have no idea whether it is even possible to do this "table copy", but all
indexes need to copy as well at least when I copy them back to the main SQL
Server.

On another note, how do I split the database into more files?  I built it
originally in 4 files, and then as I finished the import I shrank them down
into two files (on two physical 250g disks).  I am now pushing the size up
such that I need to split them back out to at least one more 250g disk (one
more file), and perhaps even two.  I haven't discovered how to split the
database into more physical files.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/





More information about the dba-SQLServer mailing list