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

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

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

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://darkslategrey-bat-805937.hostingersite.com)