1

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?

Peter Blazsik
  • 77
  • 3
  • 11
  • You should use alter table, not update. visit https://stackoverflow.com/questions/92082/add-a-column-with-a-default-value-to-an-existing-table-in-sql-server – Milad Aghamohammadi Oct 03 '18 at 09:31
  • This has already been answered [here](https://stackoverflow.com/questions/92082/add-a-column-with-a-default-value-to-an-existing-table-in-sql-server), still works the same with 2014. – Yann G Oct 03 '18 at 09:32
  • 2
    Possible duplicate of [Add a column with a default value to an existing table in SQL Server](https://stackoverflow.com/questions/92082/add-a-column-with-a-default-value-to-an-existing-table-in-sql-server) – Diado Oct 03 '18 at 09:32
  • Hi Peter, greetings from Vienna, welcome on Stackoverflow! Check out [this link](https://meta.stackoverflow.com/a/271056/9406040) on how to ask a good SQL question – Roman Oct 03 '18 at 10:24

3 Answers3

5

Something Like below:

alter table myTable add myNewColumn nvarchar(20) default 'myDefaultValue' not null
apomene
  • 14,282
  • 9
  • 46
  • 72
2

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 'info@company.com' NOT NULL;

Check the result

SELECT * FROM employees;

employee_id last_name   first_name  salary  email
1           Miller      Peter       80000   info@company.com
2           Myer        Joana       90000   info@company.com

Here is a SQL fiddle with this example.

Roman
  • 4,744
  • 2
  • 16
  • 58
1

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;
Prakash Choudhary
  • 101
  • 6
  • This creates a **new table** with default values, yes. But it does not update an **existing table**, which I think OP was after. – Roman Oct 03 '18 at 10:33
  • Please try now I Added Alter Query also – Prakash Choudhary Oct 03 '18 at 10:39
  • Hey Prakash, your SQL statement is faulty. You are adding a constraint instead of a column (and also reference `#employees` instead of `employees`). Happy coding! – Roman Oct 04 '18 at 14:11