Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - "ALTER TABLE ... ADD" - Adding New Columns to Existing Tables

By: FYIcenter.com

(Continued from previous topic...)

How To Add a New Column to an Existing Table with "ALTER TABLE ... ADD"?

If you have an existing table with existing data rows, and want to add a new column to that table, you can use the "ALTER TABLE ... ADD" statement. The tutorial script below shows you a good example:

ALTER TABLE tip ADD author VARCHAR(40)
GO

sp_columns tip
GO
TABLE_OWNER TABLE_NAME COLUMN_TABLE TYPE_NAME ...
dbo         tip        id           int       ...
dbo         tip        subject      varchar   ...
dbo         tip        description  varchar   ...
dbo         tip        create_date  datetime  ...
dbo         tip        author       datetime  ...

SELECT * FROM tip
GO
id  subject    description              create_date author
1   Learn SQL  Visit dev.fyicenter.com  2006-07-01  NULL

This SQL script added a new column called "author" to the "tip" table. NULL values were added to this column on all existing data rows.

(Continued on next topic...)

1. What is a table?
2. What are DDL (Data Definition Language) statements for tables?
3. How to create new tables with "CREATE TABLE" statements?
4. How To Get a List of All Tables with "sys.tables" View?
5. How To Get a List of Columns using the "sys.columns" View?
6. How To Get a List of Columns using the "sp_columns" Stored Procedure?
7. How To Get a List of Columns using the "sp_help" Stored Procedure?
8. How To Generate CREATE TABLE Script on an Existing Table?
9. How to create new tables with "SELECT ... INTO" statements?
10. How To Add a New Column to an Existing Table with "ALTER TABLE ... ADD"?
11. How To Delete an Existing Column in a Table with "ALTER TABLE ... DROP COLUMN"?
12. How to rename an existing column with the "sp_rename" stored procedure?
13. How to rename an existing column with SQL Server Management Studio?
14. How to change the data type of an existing column with "ALTER TABLE" statements?
15. How to rename an existing table with the "sp_rename" stored procedure?
16. How To Drop an Existing Table with "DROP TABLE" Statements?