help required w.r.t Alter table statements

[Anand Subramanian]

Hello ,
I am having a problem related to Alter table syntax in MS SQL Server .
What I want to do is in a single alter table statement I want to add as well as modify columns in a table.

For E.g.
alter table testtable add fld1 numeric(10),
alter column fld2 numeric(15)
But I am getting an error.are these kinds of statements possible in MSSQL server ?I was able to do so in Oracle.I just wanted some confirmation or examples of whether this is possible in MS SQL Server also.
Fast Help would be extremely appreciated.
Thanks,
Anand

[Ivo]

Hello
In Alter Table you can generally add columns. If you want to do something more complex, i suggest use this approach :
EXEC sp_rename <old table>, <old_table_tag>
CREATE TABLE new_table <with structure you want>
INSERT new_table <columns> SELECT <something like bridge> FROM <old_table_tag>
DROP TABLE <old_table_tag>

Is this understandable ?
If not, or you have more very speed questions, you can e-mail me personally, I will try to answer asap.
Bye


------------
Anand Subramanian at 8/7/01 3:41:14 PM

Hello ,
I am having a problem related to Alter table syntax in MS SQL Server .
What I want to do is in a single alter table statement I want to add as well as modify columns in a table.

For E.g.
alter table testtable add fld1 numeric(10),
alter column fld2 numeric(15)
But I am getting an error.are these kinds of statements possible in MSSQL server ?I was able to do so in Oracle.I just wanted some confirmation or examples of whether this is possible in MS SQL Server also.
Fast Help would be extremely appreciated.
Thanks,
Anand

[Patrick]

At the best of my knowledge, the alter column is not supported by the 6.5 version.
Unfortunatly, this forum talk about 6.5.
MsSqlServer 7.0 or 2K offer this functionnality.
Cheers
Patrick


------------
Ivo at 8/8/01 3:56:08 AM

Hello
In Alter Table you can generally add columns. If you want to do something more complex, i suggest use this approach :
EXEC sp_rename <old table>, <old_table_tag>
CREATE TABLE new_table <with structure you want>
INSERT new_table <columns> SELECT <something like bridge> FROM <old_table_tag>
DROP TABLE <old_table_tag>

Is this understandable ?
If not, or you have more very speed questions, you can e-mail me personally, I will try to answer asap.
Bye


------------
Anand Subramanian at 8/7/01 3:41:14 PM

Hello ,
I am having a problem related to Alter table syntax in MS SQL Server .
What I want to do is in a single alter table statement I want to add as well as modify columns in a table.

For E.g.
alter table testtable add fld1 numeric(10),
alter column fld2 numeric(15)
But I am getting an error.are these kinds of statements possible in MSSQL server ?I was able to do so in Oracle.I just wanted some confirmation or examples of whether this is possible in MS SQL Server also.
Fast Help would be extremely appreciated.
Thanks,
Anand