[dba-SQLServer] Migrating tables to a new database

Just an FYI.  It took two hours two minutes to copy 53 million records
consisting of the just the PK, Fname, Mname and Lname from one table in one
database to a table in another database - with indexes on only an integer
FK, a currently empty MatchCode field and an autoincrement PK being created
as well.

I do not have any experience to tell me whether this is fast, slow or
indifferent

The system is a dual core AMD X64 2.8 ghz processor with 3 gb ram and a 1
tbyte raid 6 volume, running on Windows2003.


John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Monday, January 22, 2007 8:06 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Migrating tables to a new database

Yea, that was what I was afraid of.  I really need to learn the syntax and
method of dropping indexes and rebuilding indexes so that I can build
scripts to do this.  Then perhaps the text of the scripts would be portable.

I am really just learning how to work with SQL Server.  It is kind of clunky
in some ways.  For example you can select and copy field definitions from
one table to another, even across databases, but you cannot do the same for
the view fields.  

I am going to have to learn where the breakeven point is to dropping indexes
before "bulk" adding data to tables.  In the future I will have some tables
like people and addresses where there are tens or even hundreds of millions
of records.  I will then be adding thousands or millions of new records at a
time.  Each of these tables often have indexes on pretty much every field (3
to 7 indexes).  If I want the database to remain usable while I add the new
records, i.e. respond to queries in a timely manner using the indexes, then
it seems that leaving the indexes in place while I add the new data would be
required.  I will have to discover how long it takes to add back half a
dozen indexes on a hundred million records.  Perhaps all of this will have
to be relegated to the middle of the night.  

So much to know, so little time.

John W. Colby
Colby Consulting
www.ColbyConsulting.com