Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    35

    Unanswered: adding a column then using it in a script

    Hi,
    I would like to alter a table in a sql server database then update the column with data in the same script. However, the database does not recognize the database column if I create it within the script. Is there a way to refresh this within the script so that I can run this in one procedure? If I create the table in one script then update in a second script it will work. Thanks.


    Laura

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Really?

    Code:
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
    
    INSERT INTO myTable99(Col2) SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C'
    
    SELECT * FROM myTable99
    
    ALTER TABLE myTable99 ADD Col3 int
    
    SELECT * FROM myTable99
    
    UPDATE myTable99 SET Col3 = 1
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO

    Why not post your code?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: adding a column then using it in a script

    I think you were using the generated script from sql, made changes to it and tried to execute it. You placed the update or insert after begin trans but before commit so it didn't recognize the new column.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: adding a column then using it in a script

    Originally posted by joejcheng
    I think you were using the generated script from sql, made changes to it and tried to execute it. You placed the update or insert after begin trans but before commit so it didn't recognize the new column.
    Did you cut and paste my code?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: adding a column then using it in a script

    I am talking about what Laura might have done. My guess is she cut and paste the script generated by SQL and put in an insert or update statement between BEGIN TRANS and COMMIT.

  6. #6
    Join Date
    Jan 2004
    Posts
    35
    Commit! Duh! Sometimes you overlook the easy stuff. Ok Thank you so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •