Home

Transact-SQL: Adding a Column to a Table

 

Introduction

To add a new column to a table, follow this formula:

ALTER TABLE TableName
ADD ColumnName Properties

Here is an example:

ALTER TABLE StaffMembers
ADD Address varchar(100) NULL
GO

When this code is executed, a new column named Address, of type varchar, with a limit of 100 characters, and that allows empty entries, would be added to the table named StaffMembers.

To use sample code, first display an empty query window and display the Templates Explorer. Expand the Table node. Under Table, drag Add Column and drop it in the query window. Delete the undesired sections of code and keep only the part that deals with adding a column. Here is an example:

--==========================================================================
-- Add column template
--
-- This template creates a table, then it adds a new column to the table.
--==========================================================================
USE <database, sysname, AdventureWorks>
GO

-- Add a new column to the table
ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
	ADD <new_column_name, sysname, column3>
	    <new_column_datatype,, datetime>
	    <new_column_nullability,, NULL>
GO
 

Home Copyright © 2008-2011 FunctionX.com