Post reply

Add New column and Update column in same script!

  • May 30, 2013 at 4:57 pm

    #273344

    Hi All,

    i have a script as following.

    IF COLUMNPROPERTY( OBJECT_ID('test2.dbo.Fldr'),'ColumnName','old_pk') IS NULL

    BEGIN

    ALTER Table2

    ADD old_pk INT

    END ;

    UPDATE T2

    SET

    T2.ID = T1.ID

    ,T2.old_pk = T1.[fldr_id]

    FROM Table1 T1

    INNER JOIN Table2 T2 ON T1.[ID] = T2.[ID]

    when i execute the following i get the following error

    Msg 207, Level 16, State 1, Line 39

    Invalid column name 'old_pk'.

    Everything works ok when i run the alter and update separately. but i need to do this in the same script in one go.how do i solve this?

  • May 30, 2013 at 5:32 pm

    #1619939

    Use a batch separator (a.k.a. GO).

    This will help you unless you're using the query inside a SP.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 30, 2013 at 6:06 pm

    #1619940

    Worked like a charm!

    Thank you so much.

  • June 1, 2013 at 7:42 am

    #1620281

    Luis Cazares (5/30/2013)

    Use a batch separator (a.k.a. GO).

    This will help you unless you're using the query inside a SP.

    +1 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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