CODEWITHSUNDEEP
sqlsql-server
Peter Blazsik
Peter Blazsik

Reputation: 77

How to add a column in MSSQL with default value?

In MSSQL 2014 I would like to use an update syntax which is adding a new column with a default value. Is this possible somehow?

Upvotes: 1

Views: 3850

Answers (3)

Prakash Choudhary
Prakash Choudhary

Reputation: 101

Try This :-

CREATE TABLE employees
( employee_id INT NOT NULL DEFAULT 0,
  last_name VARCHAR(50) NOT NULL DEFAULT 'jon',
  first_name VARCHAR(50) NOT NULL DEFAULT 'jona',
  salary MONEY DEFAULT 0
);


ALTER TABLE employees ADD designation VARCHAR(50) DEFAULT 'TL' NOT NULL;

Upvotes: 1

Roman
Roman

Reputation: 4999

Here is a complete reproducible example.

Create table

CREATE TABLE employees
( employee_id INT NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  salary MONEY
);

INSERT INTO employees
VALUES (1, 'Miller', 'Peter', 80000);

INSERT INTO employees
VALUES (2, 'Myer', 'Joana', 90000);

Check contents

SELECT * FROM employees;

employee_id last_name   first_name  salary  
1           Miller      Peter       80000   
2           Myer        Joana       90000

Add new column with default value

ALTER TABLE employees
ADD email VARCHAR(50) DEFAULT '[email protected]' NOT NULL;

Check the result

SELECT * FROM employees;

employee_id last_name   first_name  salary  email
1           Miller      Peter       80000   [email protected]
2           Myer        Joana       90000   [email protected]

Here is a SQL fiddle with this example.

Upvotes: 2

apomene
apomene

Reputation: 14389

Something Like below:

alter table myTable add myNewColumn nvarchar(20) default 'myDefaultValue' not null

Upvotes: 5

Related Questions