SQL SERVER – How to Add Multiple New Columns to Table with Default Values?

This is a follow up blog post of my earlier blog post on the same subject. You can read my earlier blog post over here. I recently received an email where a user asked how to add more than one new column to an existing table with a default value. Very interesting and simple question. Honestly, I love simple questions as they are the most needed by users. So here is the question – “How to Add Multiple New Columns to Table with Default Values?”. Now let us try to solve this question with the help of an example.

First, let us setup the environment. We will create a table with a single column.

<p style="text-align: justify;">-- Creating a new table
CREATE TABLE TestTable
(FirstCol INT NOT NULL)
GO
INSERT INTO TestTable (FirstCol)
SELECT 1
UNION ALL
SELECT 2
GO
-- Retrieve Data from Table
SELECT *
FROM TestTable
GO

SQL SERVER - How to Add Multiple New Columns to Table with Default Values? multicoladd1

Now let us see a simple example where we will add a single column with default value.

-- Adding a Single Column with Default Value
ALTER TABLE TestTable
ADD SingleCol INT NOT NULL DEFAULT(0)
GO
-- Retrieve Data from Table
SELECT *
FROM TestTable
GO

SQL SERVER - How to Add Multiple New Columns to Table with Default Values? multicoladd2

Now let us now see an example where we will add multiple column with default value. This time we have selected two different datatype for the columns which we are adding.

-- Adding a Multiple Column with Default Value
ALTER TABLE TestTable
ADD MultiCol1 VARCHAR(100) NOT NULL DEFAULT('MultiColVal'),
MultiCol2 INT NOT NULL DEFAULT(999)
GO
-- Retrieve Data from Table
SELECT *
FROM TestTable
GO

SQL SERVER - How to Add Multiple New Columns to Table with Default Values? multicoladd3

I hope you find this example simple enough to follow. Let me know if you have ever faced a similar situation in your daily life.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Patch Upgrade – Failed to connect to server. Error: 0x80070422
Next Post
SQL SERVER – How to Query Active Directory Data Using ADSI / LDAP Linked Server

Related Posts

8 Comments. Leave new

  • Pinal, I love your articles and tips. But why is your website covered halfway with your picture and page header? Makes it very hard to see the text and article.

    Reply
    • Hi Dan,

      What resolution do you use? This will help me debug. The goal is to have banner and menu covering only 20%

      Reply
  • This will accept the default value if the newly added columns are not nullable. If not null is not specified in alter statement, the new columns will get null value like the below.
    create table test( id int);
    alter table Test add col3 int default(3),col4 int default (4);

    Please suggest.

    -Mithun

    Reply
  • Hi Pinal, could you please advise how to set the default value of a column equals to concatenation of the other column using DDL. We want to override the column if we want, so we can’t use computed column.

    Reply
    • Create Table TestTable
      (Id int identity (1,1),
      FirstName Varchar(10) NULL,
      LastName Varchar(10) NULL,
      FullName as FirstName +’ ‘ + LastName)

      Reply
    • Create Table TestTable2
      (Id int identity (1,1),
      FirstName Varchar(10) NULL,
      LastName Varchar(10) NULL,
      FullName as CONCAT(FirstName,’ ‘,LastName))

      Reply
  • Good Sir

    Reply

Leave a Reply

Menu