Re: best practice in upgrading db structure

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