RE: Inserting Columns in an existing table 1

[allyne]

Hi EveryOne

I'm using SQL 7.0 and Have a table that I need to add columns to within a stored procedure. For example:

The current table is:

IcData (table Name)
MIN (column name)
MOU (column name)

What I need to do is insert two new columns (Home, Carrier Code) using SQL within a stored procedure so that the table looks like:

IcData (table Name)
MIN (column name)
MOU (column name)
Home (column name)
Carrier code (column name)

The only command that I can find in SQL Server Books on Line is object.InsertColumn(Column,InsertBeforeColumn)
Can't get this to work.

Thanks For all your help!
Cathy

 

[rajeevnandanmishra]

Hi Cathy,
You can use this command:
alter table IcData add Home myDataType null,
Carrier_code myDataType null

But keep in mind that, Any stored procedure or view using the command select * from IcData have to dropped and re-created to use these new columns.

 

[xcata]

Hi,
The correct syntax is (ypu must execute it in query analyzer)
ALTER TABLE IcData ADD Home VARCHAR(20) NULL
GO
ALTER TABLE IcData ADD Carrier_code VARCHAR(10) NULL
GO

you may want to change the type of each column added
the syntax you have found in books online is for SQLDMO a com object who give access to sql server objects.

 

[allyne]

Thanks everyone for all your help. I tried both ways and both worked!

Thanks Again,

Cathy