Altering a Table by Adding a Column

SQL-DMO

SQL-DMO

Altering a Table by Adding a Column

These examples illustrate adding columns to an existing Microsoft® SQL Server™ table.

Examples
A. Adding a Column Defined on a Base Data Type

The example illustrates creating a column that does not allow NULL. The provided default value is used to populate existing rows in the table.

Dim tableProducts As SQLDMO.Table

' Create a Column object, then populate it to define a column
' called ShelfLife.
Dim colShelfLife As New SQLDMO.Column
colShelfLife.Name = "ShelfLife"
colShelfLife.Datatype = "smallint"
colShelfLife.AllowNulls = False
colShelfLife.DRIDefault.Text = "31"

' Get the Products table. Note: Create and connect of SQLServer
' object used is not illustrated in this example.
Set tableProducts = _ 
oSQLServer.Databases("Northwind").Tables("Products")

' Mark start of change unit.
tableProducts.BeginAlter

' Add the populated Column object to its containing collection.
tableProducts.Columns.Add colShelfLife

' Create the column by committing the unit of change.
tableProducts.DoAlter
B. Adding a Computed Column

This example illustrates altering a table, adding a column that perform simple multiplication of the values in two other columns.

Dim tableProducts As SQLDMO.Table

' Create a Column object and populate it to define a new column
' called StockValue.
Dim colStockValue As New SQLDMO.Column
colStockValue.Name = "StockValue"
colStockValue.IsComputed = True
colStockValue.Datatype = "money"
colStockValue.ComputedText = "UnitsInStock * UnitPrice"

' Get the Products table. Note: Create and connect of SQLServer
' object used is not illustrated in this example.
Set tableProducts = _ 
oSQLServer.Databases("Northwind").Tables("Products")

' Mark start of change unit.
tableProducts.BeginAlter

' Add the populated Column object to its containing collection.
tableProducts.Columns.Add colStockValue

' Create the column by committing the unit of change.
tableProducts.DoAlter

See Also

Column Object