Altering a Table by Adding a Column (SQL-DMO)

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