Insert default value for newly adding column in existing records in table in SQL Server.

Here we see how we insert default values in newly added column in table which already has some records in it.

Sometime we are in situation that we need to add new column and we need to assign default property to that column. If the table in which we add new column is empty then we don’t have an issue to set default values but if that table already having some records and we are adding new column with default value then either we need to be empty the table or update the records with default values.

Now in this case if there is lots of record in table then it is not easy to do this. But we have one simple way to come out of this very easily.

Simply create one table with two columns, insert some records into it:

CREATE TABLE TestTable

(

      ID INT,

      Name VARCHAR(10)

)

INSERT INTO TestTable

VALUES(1,'PP'),(2,'AA')

SELECT * FROM TestTable

Output:

ID	Name
1	PP
2	AA

Now we are adding new column with default values in existing records using:

ALTER TABLE TestTable

ADD newColumn BIT DEFAULT(0) WITH VALUES

SELECT * FROM TestTable

Output:

ID	Name	newColumn
1	PP	0
2	AA	0