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)