Have you ever wondered how to add more than one new column to an existing table in SQL Server with default values? It’s a common question that many users have, and in this blog post, we will explore a simple solution to this problem.

Let’s start by setting up the environment. We will create a table with a single column:

-- Creating a new table
CREATE TABLE TestTable
(FirstCol INT NOT NULL)
GO
INSERT INTO TestTable (FirstCol)
SELECT 1
UNION ALL
SELECT 2
GO

Now, let’s see a simple example where we add a single column with a default value:

-- Adding a Single Column with Default Value
ALTER TABLE TestTable
ADD SingleCol INT NOT NULL DEFAULT(0)
GO

In the above example, we used the ALTER TABLE statement to add a new column called SingleCol to the TestTable. The column has a data type of INT and a default value of 0.

If we retrieve the data from the table now, we will see the new column with the default value:

-- Retrieve Data from Table
SELECT *
FROM TestTable
GO

Now, let’s move on to adding multiple columns with default values:

-- Adding Multiple Columns with Default Values
ALTER TABLE TestTable
ADD MultiCol1 VARCHAR(100) NOT NULL DEFAULT('MultiColVal'),
MultiCol2 INT NOT NULL DEFAULT(999)
GO

In the above example, we added two new columns to the TestTable. The first column, MultiCol1, has a data type of VARCHAR(100) and a default value of ‘MultiColVal’. The second column, MultiCol2, has a data type of INT and a default value of 999.

If we retrieve the data from the table again, we will see the new columns with their respective default values:

-- Retrieve Data from Table
SELECT *
FROM TestTable
GO

I hope you found this example simple enough to follow. Adding multiple new columns to a SQL Server table with default values is a straightforward process using the ALTER TABLE statement. Let me know if you have ever faced a similar situation in your daily life.