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

[vaibhav punekar]

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]

Its not advisable to have users adding columns
That said....
alter table add colname integer
Dan

[Anil Vupputuri]

Yeah there is only one option to add column is using above stmt. alter table [table_name] add [column_name] [data_type].

[vaibhav punekar]

thanks a lot.I thought there would be some other way.is there any really?

[Peter den Haan]

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

CREATE TABLE CUSTOMER (
   ID           NUMERIC NOT NULL PRIMARY KEY,
   NAME         VARCHAR2(80),
   TEL1         VARCHAR2(20),
   TEL2         VARCHAR2(20)
);

but always model it like

CREATE TABLE CUSTOMER (
   ID           NUMERIC NOT NULL PRIMARY KEY,
   NAME         VARCHAR2(80)
);
CREATE TABLE CUSTOMER_TELEPHONE (
   CUSTOMER_ID  NUMERIC NOT NULL,
   ORDER        NUMERIC NOT NULL,
   NUMBER       VARCHAR2(20) NOT NULL,
   PRIMARY KEY (CUSTOMER_ID, ORDER)
)

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]

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]

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]

good point, Peter!
-Zhining