Adding Multiple Columns to a Table With a Single T-SQL Statement

You know, I believe the old saying, “If you don’t use it you will lose it”.  Well, I write a lot of Date Manipulation (DM) T-SQL these days and very little (Data Definition) DD.  Since my projects have all completed and rolled to production, I am on the backend writing reports and doing some performance tuning.  Even when I was doing a lot of DML I never really had to do what I was asked by a developer recently.  The question was, “Can you add multiple columns to a Table with a single T-SQL Statement.  I thought to myself I am sure you can, but I wasn’t quite sure how.  So I took at stab at it:

ALTER TABLE dbo.Table1

     ADD Column1 int

     ADD Column2 int

 

Sorry that’s not correct.   I know what you are thinking, you call yourself a DBA.  Well, I really never had to do this before.  So I went directly to my reference of choice, Books Online (BOL).  I filtered my search on ALTER TABLE, scrolled down a bit and low and behold there was the answer:

 

ALTER TABLE dbo.Table1

    ADD

        Column1 int,

        Column2 int

 

Very simple solution, you only need to add the ADD keyword once, then all you need to do is delimit the column definitions with commas.  Maybe the guys at SQLServerCentral can use this as the Question of the Day.  Thanks again BOL. 

 

Talk to you soon,

Patrick LeBlanc

SQL Down South