[SOLVED]-ADDING A COLUMN WITH A DEFAULT VALUE TO AN EXISTING TABLE IN SQL SERVER
ADDING A COLUMN WITH A DEFAULT VALUE TO AN EXISTING TABLE IN SQL SERVER
We will learn, How to Add a column with a default value to an existing table in SQL Server.I have specially written this sql server article for newcomers developer and anyone need this query.
SQL Syntax:
ALTER TABLE {SQL_TABLENAME}
ADD {TABLE_COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUESSQL Example:
ALTER TABLE TABLENAME
ADD COLNAME Bit NULL
CONSTRAINT D_TABLENAME_COLNAME
DEFAULT (0)--Optional Default-Constraint VALUE.
WITH VALUES -- Column Vlaue is Null then add Default Value for Existing Records.Let’s take an example, I have created a table “Company_Users” with four-column Id, Name, Email, City. Now I want to add one more column ‘Country‘ with default value ‘India‘.
Sql Query:
ALTER TABLE Company_Users
ADD Country nvarchar(200) NULL
CONSTRAINT D_Company_Users_Country
DEFAULT ('India')--Optional Default-Constraint VALUE.
WITH VALUES -- Column Vlaue is Null then add Default Value for Existing Records.
Sql Table Create Script
CREATE TABLE [dbo].[Company_Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Email] [nvarchar](max) NOT NULL,
[City] [nvarchar](max) NULL,
[Country] [nvarchar](200) NULL,
CONSTRAINT [PK_TblUsers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
GO
ALTER TABLE Company_Users
ALTER COLUMN UserName DROP DEFAULT;