Tuesday, March 29, 2016

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


Sample data

CREATE TABLE [DBO].[ADD_COLUMN_TEST](
                [ID] [INT] IDENTITY(1,1) NOT NULL,
                [NAME] NVARCHAR(50) NULL,
                [COUNTRY] NVARCHAR(50) NULL
) ON [PRIMARY]

GO

now insert the data in table
INSERT INTO [ADD_COLUMN_TEST] VALUES('BRAIN','US')
INSERT INTO [ADD_COLUMN_TEST] VALUES('MICHEL','US')



BELOW SYNTAX IS USED TO ADD A COLUMN TO EXISTING TABLE WITH DEFAULT VALUE
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]

Now add column with default value to the add_column_test table

ALTER TABLE [add_column_test]
ADD newcolumn1 int NOT NULL DEFAULT(1)
GO

 If it is varchar or nvarchar use below syntax
ALTER TABLE [add_column_test] ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'NEWDATA'