Inser new column into existing Table in SQL database

[nctran]

I am using DMO in delphi to create Table and modify existing table in SQL database. I can not find any info about how to insert new column into an existing table but you can add column.

By having a look at Enterprise manager you can insert column without problem.

If anyone know how Enterprise manager does it, Please help. I am sure it uses DMO as well.

I also have an additional query about Collation property of Column in the table : If anyone experienced that when you change Collation property of a Column it also changes AllowNulls property to True also.{Vise versa }

Thanks,

 

[swampBoogie]

There is no statement for this. EM does this as several operations, create new table, copy data, delete old table, rename new table.

 

[Sabnac]

ALTER TABLE will works perfectly.
From BOL :
A. Alter a table to add a new column
This example adds a column that allows null values and has no values provided through a DEFAULT definition. Each row will have a NULL in the new column.

CREATE TABLE doc_exa ( column_a INT)
GO
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
GO
EXEC sp_help doc_exa
GO
DROP TABLE doc_exa
GO

 

[swampBoogie]

I assumed his question was about adding a column not at the end of a table.

 

[nctran]

Yes, Insert column is to add column into any position not just at the end of the column in table.

Hi swampBoogie,

I have thought about it too EM will create new one because when you have look at the create date of the table has been changed, but how they do it? :
+ its is very fast even on large table?
+ DMO does not provide any method for copying data from column to column {not that i know of}

Thanks,