MS SQL Server - How to insert a column with default value to an existing table?

1. Using SQL Query

ALTER TABLE table_name
    ADD column_name tada_type NOT NULL 
    CONSTRAINT constraint_name DEFAULT default_value;

If you set the new column nullable, that column value for all existing rows will be NULL instead of the default value. In that case, you can add WITH VALUES to the statement:

ALTER TABLE table_name
    ADD column_name data_type NULL 
    CONSTRAINT constraint_name DEFAULT default_value 
    WITH VALUES;

Example:

ALTER TABLE orders1
    ADD store_code TEXT NULL 
    CONSTRAINT store_code_d DEFAULT "store1"
    WITH VALUES;

2. Using TablePlus GUI Tool

• From data table view, switch to database structure view using the Structure button at the window bottom, or use shortcut keys Cmd + Ctrl + ].
• From the structure editor, click + Column to add a new column. You can also double-click on the empty row to insert.
• Enter your default column value at column_default field
• Hit Cmd + S to commit changes to the server.

And here is the result:

---

Need a good GUI Tool for MS SQL Server? Try TablePlus, a modern, native tool for multiple relational databases. It’s free anyway.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.