Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Can we add a Field(column) in a table dynamically?

 
vaibhav punekar
Ranch Hand
Posts: 134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
I have uptil now updated table by adding rows dynamically.I would like to add a field name(ie new column)dynamically.
The scenario is like this.
I have a administrator account who should be able to edit the table by adding new column.We have no. of premises and each premise has its own database.We wish to transfer database from one premises to other as well as admin should be able to add new premise altogether.Thanks in advance.How can I achieve this?
 
Daniel Dunleavy
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Its not advisable to have users adding columns
That said....
alter table add colname integer
Dan
 
Anil Vupputuri
Ranch Hand
Posts: 527
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yeah there is only one option to add column is using above stmt. alter table [table_name] add [column_name] [data_type].
 
vaibhav punekar
Ranch Hand
Posts: 134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks a lot.I thought there would be some other way.is there any really?
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by vaibhav punekar:
We wish to transfer database from one premises to other as well as admin should be able to add new premise altogether. [...] How can I achieve this?

The syntax is
  Oracle: ALTER TABLE table ADD (column datatype)
  SQL Server: ALTER TABLE table ADD column datatype
  Others: something else again
But having said that... re-think your database design!!! It is bad practice to design a schema in such a way that you have to modify it on a regular basis. If you have a variable number of premises, model them as such in your schema.
This is actually a general point: if you have multiple copies of something, say a telephone number associated with a customer, do not be tempted to model it like

but always model it like

unless you are very, very sure that there absolutely never will be more than a certain number of copies. But in most cases, when you hold multiple copies of something, someone will ask you for fewer or more copies.
- Peter

[This message has been edited by Peter den Haan (edited June 14, 2001).]
 
vaibhav punekar
Ranch Hand
Posts: 134
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Peter,
Do you mean that for each "ORDER" there would be unique "NUMBER"?
I got the point that the multiple record field should be separated.But then again in the second table same thing will arise that is updating it from the outside.
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by vaibhav punekar:
Thanks Peter,
Do you mean that for each "ORDER" there would be unique "NUMBER"?

That name was not happily chosen. I was thinking of a specific ordering that telephone numbers for a customer might have: 1, 2, 3... The primary key is then the combination of the customer ID and the telephone order number.
I got the point that the multiple record field should be separated.But then again in the second table same thing will arise that is updating it from the outside.

I'm not sure I understand you. The point of the example was that the second schema would be able to accomodate more or fewer telephone numbers simply through DML (Data Manipulation Language) rather than DDL (Data Definition Language) statements. Similarly, it sounded like your database should really be able to accomodate new premises using DML only.
- Peter

[This message has been edited by Peter den Haan (edited June 14, 2001).]
 
ZhiningZhang
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
good point, Peter!
-Zhining
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic