Re: best practice in upgrading db structure

["Jim C. Nasby" <jnasby(at)pervasive(dot)com>]

On Wed, Mar 29, 2006 at 12:24:04AM +0200, SunWuKung wrote:
> In article <20060328221109(dot)GC75181(at)pervasive(dot)com>, jnasby(at)pervasive(dot)com 
> says...
> > On Tue, Mar 28, 2006 at 09:28:12PM +0200, SunWuKung wrote:
> > > This is going to be an amateur question...
> > > 
> > > Could somebody explain me, or point me to a resource where I can find 
> > > out what is the recommended practice when a live db needs to be replaced 
> > > with a new version of it that has a slightly different structure?
> > > 
> > > My first guess would be to create the empty new version of the db and 
> > > insert the data of the old one into it - adding and modifying it when 
> > > necessary, but I am not sure.
> > > 
> > > What do you usually do in a situation like this?
> > 
> > ALTER TABLE ...
> > 
> 
> I know this is easy to do when the db is empty, but do you still suggest 
> this when there is data in the db : changing the structure of the old 
> version of the db to match the new one (obviously not on the live 
> server) and than try to modify the data in it to fit the structure eg. 
> there are two new columns that needs data?

Depends on how much data you need to modify. For small tables, I stick
with ALTER TABLE because it's a lot cleaner/easier. For larger tables,
you might want to CREATE TABLE AS SELECT ..., or maybe copy out and copy
back in.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461