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

Q

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

✍: FYIcenter.com

A

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.

 

"ALTER TABLE ... DROP COLUMN" - Deleting Existing Columns in SQL Server

"SELECT ... INTO" - Creating New Tables With Queries in SQL Server

Managing Tables and Columns in SQL Server

⇑⇑ SQL Server Database Tutorials

2016-11-17, 2038🔥, 0💬