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
|
