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://blog.sqlauthority.com)
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.
Hi Dan,
What resolution do you use? This will help me debug. The goal is to have banner and menu covering only 20%
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
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.
Create Table TestTable
(Id int identity (1,1),
FirstName Varchar(10) NULL,
LastName Varchar(10) NULL,
FullName as FirstName +’ ‘ + LastName)
Insert into TestTable
Select ‘Mukesh’,’Kumar’
Create Table TestTable2
(Id int identity (1,1),
FirstName Varchar(10) NULL,
LastName Varchar(10) NULL,
FullName as CONCAT(FirstName,’ ‘,LastName))
Good Sir