Wednesday, December 7, 2011

How To Add new column to existing SQL Server Table

One of my student asked me, he wanted to modify existing SQL Server table by adding new column price (money). Table already has content so he does not want to recreate the table. He tried to use SQL Server Management Studio Table Designer but could not save the modified structure.

Solution : use this select - into query to create new table

Select * , cast(0 as money) as price into dbo.newtable from dbo.oldtable

This query will create new table name dbo.newtable with the structure same as oldtable plus additional new column (price) type money, and populate dbo.newtable from dbo.oldtable content.

No comments:

Post a Comment