Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Comments

Posted by sanjeet.uchil on 13 August 2009

nice, thats why i like sql. even complex looking problems have simple solutions.

Posted by alexgmz23 on 13 August 2009

I agree with sanjeet, sometimes the more complex the problem is the more simple is the solution.

Posted by mjmoch on 13 August 2009

Can you point me in the direction of the BOL site you use as a reference?  I would love to add that site as another SQL tool.

Posted by Patrick LeBlanc on 13 August 2009

mjmock, the source of the information can be obtained from either SQL Server Books Online:  

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/f1745145-182d-4301-a334-18f799d361d1.htm

Or msdn:

msdn.microsoft.com/.../ms190273.aspx

Hope this helps.  If not let me know.

Posted by Charles Kincaid on 13 August 2009

One of the tricks that I se is the scripting feature in SSMS.  I keep a "play" database on a server just to try stuff out.

I open up the table designer, chose my test table, add a couple of columns, and generate the change script.

ALTER TABLE dbo.KrapTable ADD

AddFirstNumber bigint NULL,

AddSomeText nvarchar(MAX) NULL

Now some changes done by SSMS recreate the entire table.  Like the recent change I had to make for a warehousing project where they expanded the Pallet number column.  SSMS wanted to recreate each table when ALTER TABLE did each table in a fraction of a second.

Posted by john.decoville on 13 August 2009

Hey Patrick!

How come you make things so easy compared to MSDN?

They should hire you!

Thanks,

--John

Posted by David Walker on 13 August 2009

While we're nitpicking (we're programmers, after all): The expression is "lo and behold", not "low and behold".  

Or, from old books and pirate tales, "Lo! And behold!".  Low has nothing to do with it!

David Walker

Posted by Patrick LeBlanc on 13 August 2009

Thanks David, you know a fellow co-worker brought that to my attention and I meant to change it, but I forgot.

Thanks

Posted by Dave F on 13 August 2009

Lo and behold: means look and notice; used often in the Bible.

Low and behold: a recent movie about life in New Orleans after Katrina.

That should settle who's more powerful: Bible or Hollywood. ;=)

Posted by Amit Lohia on 13 August 2009

Now just imaging this was an interview question and someone got it wrong it does not mean they are a bad DBA.

Judging someone skill level in 1 hour interview is an art.

Posted by geerobg on 13 August 2009

~Now just imaging this was an interview question and someone got it wrong it does not mean they are a bad DBA.~

Nope... but it would be definitely indicate someone without much architecture experience. That said... I wonder how well I'd do without access to my vast compendium of scripts (I have gotten lazy as of late).

And with intellisense becoming more prevalent I can feel my gray matter slowly turning black.

Posted by Adam Tappis on 17 August 2009

No disrespect to the author, but I don't really see how something so trivial even warrants an article.

BOL should be any respectable DBA's or developers bread and butter as no-one is capable of keeping that mass of information and details in their head.

Posted by Varinder Sandhu on 31 December 2010

Really Good Article

Posted by chineduk.consultsql on 17 February 2014

Thanks Patrick LeBlanc.

Now I have a task that's been give me headache.

Let's say I have a Table B which was create from Table A. i.e ( select * from Table A into Table B).

Now every 2 or 3 day more new fileds/column are added to Table A and I am expected to update Table B with the new added columns every it happens.

What I have been doing is manually repeat ( select * from Table A into Table B) adding the new fields.

How can I do this automatically?

Thanks

Posted by Patrick_LeBlanc on 25 March 2014

chineduk.consultsqlsorry for the delayed response.  You could create a SQL Agent job that ran at some interval that executes the script.  If you wanted a more eloquent solution, you could use the system tables and compare the table schemas.  If there is a difference you can also use the same schemas to generate alter table scripts that will add the columns.  Either way I suggest using an Agent job to automate the process.

Leave a Comment

Please register or log in to leave a comment.