Share via

Adding column to existing table and populate with default value

  • Article

From time to time, we run into the scenarios of adding new columns to existing table with millions of records to meet emerging business needs. And these new columns often need initialized with default value. In this post, I’d like to illustrate my solution for such problems. Let us assume,

Database: TestDB

Existing table: TestTable

Existing columns: ID, C1, C2

And need to add,

Column name: “NewColumn”

Default value=”0”

SQLType=”INT”

Most simple solution would be:

USE TestDB;

GO

-- Add 'NewColumn' column and poplulate this column as 0

ALTER TABLE TestTable ADD NewColumn INT;

GO

UPDATE TestTable SET NewColumn = 0;

GO

Often things are not that simple. We often get other questions regarding this problem. Hereby I list them as FAQs.

Q1 : My existing table is very complicated with lots of foreign keys and indexes. Does this solution work for that?

A1: Maybe not. I strongly recommend you solve this by creating a new table with the correct order, and copy all the records from the existing one to the new one.

Q2 : Your solution will put newly-inserted columns to the last. Can we put them to certain specific places? I am asking because our business rule needs keep audit column in the end.

A2: Unfortunately, there is no quick way to add them in a specific place in the column order in T-SQL. One thing worth mentioning is that column order does not matter because you can select the columns in any order you want.

Q3 : Your solution looks fine for me, but I get hundreds of tables to do the same thing. Can I put this into a stored procedure?

A3: Yes and No. Alter table can’t take dynamic parameters. So it is impossible to create SP such as:

-- INCORRECT SAMPLE. DO NOT TAKE IT.

CREATE PROCEDURE sp_addcolumn

    @tablename varchar(50) = 0,

    @columnname varchar(50) = 0,

    @datatype varchar (50) = 0

AS

    ALTER TABLE @tablename

    ADD COLUMN @columnname @datatype

END

However we can do it in another way like this:

-- Correct sample

CREATE PROCEDURE sp_addcolumn

    @tableName VARCHAR(50) = 0,

    @colName VARCHAR(50) = 0,

    @dataType VARCHAR (50) = 0

AS

    DECLARE @tsql VARCHAR (200)

    SET @tsql = 'ALTER TABLE ' + @tableName + ' ADD ' + @colName + ' ' + @dataType

    EXEC(@tsql)

    SET @tsql = 'UPDATE ' + @tableName + ' SET ' + @colName + '= 0'

    EXEC(@tsql)

GO

-- The way to use the SP is as followings:

-- EXEC sp_addcolumn 'TestTable', 'NewColumn', 'INT'

Comments