SQL Server – Add a column with a default value to an existing table

Add a column with a default value to an existing table in SQL Server

0
121

if OBJECT_ID('tempdb..#my_travel') is not null
Begin
Drop Table #MyTravel
End

CREATE TABLE #MyTravel
(
TravelId int,
TravelCity varchar(100)
)
Insert Into #MyTravel values(1,’Pune’)
Insert Into #MyTravel values(2,’Mumbai’)
Insert Into #MyTravel values(3,’Delhi’)

GO
ALTER TABLE #MyTravel
ADD IsMetroCity int NOT NULL DEFAULT(1)
GO

Select * from #MyTravel

Above script will add existing rows with the default value 1, so the NOT NULL constraint is not violated.

LEAVE A REPLY

Please enter your comment!
Please enter your name here