T-SQL: Add column with default values to a table

Let’s say you need to add a column to an existing table that already contains some rows. And this new column needs to be prepopulated with a default value. For example integer nullable column NEW_COLUMN is added to table MY_TABLE. Common approach is to check for column existence, and if it doesn’t exist – create and populate it:

if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL
	UPDATE [dbo].[MY_TABLE] SET [NEW_COLUMN] = 1
END

But this doesn’t work – you get “Invalid column name” error. The trick is – you don’t need a separate UPDATE statement. ALTER TABLE … ADD statement has a DEFAULT parameter. You can modify previous code like this:

if not (exists(SELECT * FROM SYSCOLUMNS WHERE ID = OBJECT_ID('[dbo].[MY_TABLE]') AND Name = 'NEW_COLUMN'))
BEGIN
	ALTER TABLE [dbo].[MY_TABLE] ADD [NEW_COLUMN] [int] NULL DEFAULT 1 WITH VALUES
END

This ALTER statement assigns default value of 1 to the new column. WITH VALUES parameter is important, it populates existing rows with the default value (without it it will be populated by NULLs).

One reply

  1. NICE! This little Gem saved me a great deal of time now and will save me much more time in the future!

    Thank You Yuriy

Leave a Reply

Your email address will not be published. Required fields are marked *