Post reply

Adding a single column to multiple tables

  • September 16, 2011 at 10:35 am

    #257395

    I have 15 tables that need the same column i.e. the column is identical in its and i have to do this via a sql script

    Can i add all these columns to multiple tables with a single alter, and then update them with the same value. Please if anyone can point me anywhere that would be awesome.

  • September 16, 2011 at 10:57 am

    #1382678

    You can do it with a single script but not a single alter.

    Each table affected will need to have an alter statement.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 16, 2011 at 11:18 am

    #1382689

    Thanks Jason

    as i sort of knew it was hihgly unlikely but i just wanted to see if someone had an idea

    thanks again

  • September 16, 2011 at 11:26 am

    #1382693

    you can wrap the commands for all 15 alters in a single transaction though...so you can make sure it's all or nothing.

    SET XACT_ABORT ON --bailout and rollback on any error in a transaction

    BEGIN TRAN

    ALTER TABLE Orders ADD CreatedDate datetime default getdate()

    ALTER TABLE Orders2 ADD CreatedDate datetime default getdate()

    --etc

    COMMIT TRAN

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply