SQL Server/T-SQL/Table/Add Column
Add columns to a table
<source lang="sql">
1> create table employee( 2> ID int, 3> name nvarchar (10), 4> salary int, 5> start_date datetime, 6> city nvarchar (10), 7> region char (1)) 8> GO 1> 2> insert into employee (ID, name, salary, start_date, city, region) 3> values (1, "Jason", 40420, "02/01/94", "New York", "W") 4> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (2, "Robert",14420, "01/02/95", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (4, "Linda", 40620, "11/04/97", "New York", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (5, "David", 80026, "10/05/98", "Vancouver","W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (6, "James", 70060, "09/06/99", "Toronto", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (7, "Alison",90620, "08/07/00", "New York", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W") 3> GO (1 rows affected) 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected) 1> 2> -- Adds the column telephone_no to the employee table. 3> ALTER TABLE employee 4> ADD telephone_no CHAR(12) NULL 5> 6> select * from employee 7> GO ID name salary start_date city region telephone_no
---------- ----------- ----------------------- ---------- ------ ------------
1 Jason 40420 1994-02-01 00:00:00.000 New York W NULL
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N NULL
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W NULL
4 Linda 40620 1997-11-04 00:00:00.000 New York N NULL
5 David 80026 1998-10-05 00:00:00.000 Vancouver W NULL
6 James 70060 1999-09-06 00:00:00.000 Toronto N NULL
7 Alison 90620 2000-08-07 00:00:00.000 New York W NULL
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N NULL
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W NULL
(9 rows affected) 1> 2> drop table employee 3> GO 1>
</source>
Add to columns to a table then check the table structure
<source lang="sql">
3> 4> 5> CREATE TABLE Employees 6> ( 7> EmployeeID int IDENTITY NOT NULL, 8> FirstName varchar(25) NOT NULL, 9> MiddleInitial char(1) NULL, 10> LastName varchar(25) NOT NULL, 11> Title varchar(25) NOT NULL, 12> SSN varchar(11) NOT NULL, 13> Salary money NOT NULL, 14> PriorSalary money NOT NULL, 15> LastRaise AS Salary - PriorSalary, 16> HireDate smalldatetime NOT NULL, 17> TerminationDate smalldatetime NULL, 18> ManagerEmpID int NOT NULL, 19> Department varchar(25) NOT NULL 20> ) 21> GO 1> 2> 3> EXEC sp_help Employees 4> GO Name Owner
Type Created_datetime
-----------------------------------------------------------------------
------------------------------- -----------------------
Employees dbo
user table 2008-08-17 13:06:15.063
Column_name Type
Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNu
llInSource Collation
-----------------------------------------------------------------------
----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------
--------------------------------------------------------------------------------------------------------------------------------
EmployeeID int
no 4 10 0 no (n/a) (n/a)
NULL
FirstName varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
MiddleInitial char
no 1 yes no yes
SQL_Latin1_General_CP1_CI_AS
LastName varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
Title varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
SSN varchar
no 11 no no no
SQL_Latin1_General_CP1_CI_AS
Salary money
no 8 19 4 no (n/a) (n/a)
NULL
PriorSalary money
no 8 19 4 no (n/a) (n/a)
NULL
LastRaise money
yes 8 19 4 yes (n/a) (n/a)
NULL
HireDate smalldatetime
no 4 no (n/a) (n/a)
NULL
TerminationDate smalldatetime
no 4 yes (n/a) (n/a)
NULL
ManagerEmpID int
no 4 10 0 no (n/a) (n/a)
NULL
Department varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment
Not For Replication
---------------------------------------- ------------------------------
-------------------
EmployeeID 1
1 0
RowGuidCol
No rowguidcol column defined. Data_located_on_filegroup
PRIMARY The object "Employees" does not have any indexes, or you do not have permissions. No constraints are defined on object "Employees", or you do not have permissions. No foreign keys reference table "Employees", or you do not have permissions on referencing tables. Table is referenced by views
1> ALTER TABLE Employees 2> ADD 3> DateOfBirth datetime NULL, 4> LastRaiseDate datetime NOT NULL 5> DEFAULT "2000-01-01" 6> 7> GO 1> 2> EXEC sp_help Employees 3> GO Name Owner
Type Created_datetime
-----------------------------------------------------------------------
------------------------------- -----------------------
Employees dbo
user table 2008-08-17 13:06:15.063
Column_name Type
Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNu
llInSource Collation
-----------------------------------------------------------------------
----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------
--------------------------------------------------------------------------------------------------------------------------------
EmployeeID int
no 4 10 0 no (n/a) (n/a)
NULL
FirstName varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
MiddleInitial char
no 1 yes no yes
SQL_Latin1_General_CP1_CI_AS
LastName varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
Title varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
SSN varchar
no 11 no no no
SQL_Latin1_General_CP1_CI_AS
Salary money
no 8 19 4 no (n/a) (n/a)
NULL
PriorSalary money
no 8 19 4 no (n/a) (n/a)
NULL
LastRaise money
yes 8 19 4 yes (n/a) (n/a)
NULL
HireDate smalldatetime
no 4 no (n/a) (n/a)
NULL
TerminationDate smalldatetime
no 4 yes (n/a) (n/a)
NULL
ManagerEmpID int
no 4 10 0 no (n/a) (n/a)
NULL
Department varchar
no 25 no no no
SQL_Latin1_General_CP1_CI_AS
DateOfBirth datetime
no 8 yes (n/a) (n/a)
NULL
LastRaiseDate datetime
no 8 no (n/a) (n/a)
NULL
Identity Seed Increment
Not For Replication
---------------------------------------- ------------------------------
-------------------
EmployeeID 1
1 0
RowGuidCol
No rowguidcol column defined. Data_located_on_filegroup
PRIMARY The object "Employees" does not have any indexes, or you do not have permissions. constraint_type constraint_name
delete_action update_action status_enabled status_for_replication constraint_keys
-----------------------------------------------------
------------- ------------- -------------- ---------------------- ----------------------------------------------------------
DEFAULT on column LastRaiseDate DF__Employees__LastR__0F0E1094
(n/a) (n/a) (n/a) (n/a) ("2000-01-01")
No foreign keys reference table "Employees", or you do not have permissions on referencing tables.
Table is referenced by views
1> drop table Employees; 2> GO 1>
</source>
Alter table to add column
<source lang="sql">
1> -- 2> 3> CREATE TABLE Appointment 4> ( AppointmentID Int 5> , Description VarChar(50) 6> , StartDateTime DateTime 7> , EndDateTime DateTime 8> , Resource VarChar(50) Null 9> ) 10> GO 1> 2> ALTER TABLE Appointment 3> ADD LeadTime SmallInt Null 4> GO 1> 2> drop table Appointment 3> GO 1>
</source>
