Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 7 of 7 total |
Alter Table from an existent structure |
Wed, Sep 24 2008 12:14 PM | Permanent Link |
Barbazza Dario | HI,
I would to alter a table by the structure of another one. Is there a function to do this or I must create a sql string?? In dbisam 4 I can write: myTable.FieldDefs.Assign(TableModel.FieldDefs) myTable.IndexDefs.Assign(TableModel.IndexDefs) myTable.AlterTable This just to explain you my needed. I have written the procedure to create the SQL string but I have some doubts when I must change the position of some columns; The 'ALTER TABLE' sql command adds before all the columns and then change the position?? In this case must I pass the position's columns from the lowest to the highest or the engine doesn't need this?? or what is the priority of command execution in an "alter table" sql command?? Thank you Dario |
Wed, Sep 24 2008 1:05 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Barbazza
>Is there a function to do this or I must create a sql string?? You have to create an sql string. All DDL type commands (eg alter table, create index etc) in ElevateDB are sql. >I have written the procedure to create the SQL string but I have some doubts when I must change the position of some columns; >The 'ALTER TABLE' sql command adds before all the columns and then change the position?? >In this case must I pass the position's columns from the lowest to the highest or the engine doesn't need this?? Altering column positions is always fun. I haven't asked or seen anything about this so I assume its much the same as DBISAM (slightly different syntax). I find it useful to look in Explorer-SQL History in EDBManager eg ALTER TABLE "Billing" ADD COLUMN "_Sequencex" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL, ALTER COLUMN "_Sequencex" MOVE TO 38, ALTER COLUMN "_Sequencex" MOVE TO 37, ALTER COLUMN "_Sequencex" MOVE TO 36 You can take ALTER COLUMN "_Sequencex" MOVE TO 38, ALTER COLUMN "_Sequencex" MOVE TO 37, out since only the final MOVE matters. However, you need to get the MOVEs in the right sequence. If you MOVE column A to 4 then MOVE column B to 3 (and it was at 10) then column A will be at 5. Roy Lambert [Team Elevate] |
Thu, Sep 25 2008 3:32 AM | Permanent Link |
"Mauro Botta" | i have this, too.
i have 2 files. File A is a full table with data ( old structure ) , File B is a empty table with new structure ( new field, field moved up/down..,del field ) How i can upgrade structure of A with fields/index... of File B ? |
Thu, Sep 25 2008 3:58 AM | Permanent Link |
Barbazza Dario | [...]
> However, you need to get the MOVEs in the right sequence. If you MOVE column A to 4 then MOVE column B to 3 (and it was at 10) then column >A will be at 5. Yeah this is the problem!! ^^ I thinked so but now I have the confirm.. Thank you very much to explain me and to understand what I have write in my "fun" English ^^ Dario |
Thu, Sep 25 2008 4:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Mauro
Depends on just what you mean. Should A end up looking the same as B or should A have additional fields added or..... Roy Lambert [Team Elevate] |
Thu, Sep 25 2008 6:57 AM | Permanent Link |
"Mauro Botta" | > Depends on just what you mean. Should A end up looking the same as B or
> should A have additional fields added or..... I must update db structure of my clients. Table A is Table with data of my clients ( example... : invoice.edbtbl ) Table B is always invoice.edbtbl , with all field of A , but with any new fields. original : Table A Table B FIELD_A FIELD_A FIELD_B FIELD_B FIELD_C FIELD_B2_NEW FIELD_D FIELD_C FIELD_E FIELD_D ..... .... i need this : Table A Table B FIELD_A FIELD_A FIELD_B FIELD_B FIELD_B2_NEW FIELD_B2_NEW FIELD_C FIELD_C FIELD_D FIELD_D ..... .... With DBISAM is more more more easy. (* myTable.FieldDefs.Assign(TableModel.FieldDefs) myTable.IndexDefs.Assign(TableModel.IndexDefs) myTable.AlterTable *) How i can update ( ....easy.... ) Update Structure of A , getting all structure from B ? Note * In Table B , any time , i Move , up and down any fields... without adding any new fields. * i can make only one altertable procedure ( i need speed , my db are big ) * ALTER COLUMN "_Sequencex" MOVE TO 37 .... This mode is too complex.. another method ? .. |
Thu, Sep 25 2008 7:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Mauro
>With DBISAM is more more more easy. There are a number of things easier with DBISAM - as Tim will tell you I've moaned about them frequently. >How i can update ( ....easy.... ) Update Structure of A , getting all >structure from B ? There is only way that I can think of to do this which is to query the catalog for both tables, work out the differences and build sql to do the alterations. >Note >* In Table B , any time , i Move , up and down any fields... without adding >any new fields. > >* i can make only one altertable procedure ( i need speed , my db are big ) > >* ALTER COLUMN "_Sequencex" MOVE TO 37 .... > This mode is too complex.. another method ? I don't understand what you mean here. How is it to complex? You could always look at a 3rd party tool eg http://www.contextsoft.com/products/dbdesign/ Roy Lambert [Team Elevate] |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |