SQL Server Performance

Alter temp table?

Discussion in 'General Developer Questions' started by fractallian, Apr 22, 2005.

  1. fractallian New Member

    Can anyone tell me what is going on here?

    CREATE TABLE #results (
    mfr varchar(200),
    partno varchar(50),
    productname varchar(200)
    )
    INSERT INTO #results (mfr, partno, productname) VALUES ('Adaptec', '231234', 'PCI Card')

    ALTER TABLE #results ADD test int

    SELECT * FROM #results

    Gives me:

    mfr partno productname test
    -------------------------------------
    Adaptec231234PCI CardNULL
    But when I try to:

    UPDATE #results SET test = 1
    I get:
    "Invalid column name 'test'."
    Why does the alter statement appear to work but I can't update the added column?

  2. Madhivanan Moderator

    You need to use Go keyword
    Try this

    drop table #results
    go
    CREATE TABLE #results (mfr varchar(200),partno varchar(50),productname varchar(200))
    INSERT INTO #results (mfr, partno, productname) VALUES ('Adaptec', '231234', 'PCI Card')
    ALTER TABLE #results ADD test int
    SELECT * FROM #results
    go
    UPDATE #results SET test = 1
    select * from #results



    Madhivanan

    Failing to plan is Planning to fail
  3. dineshasanka Moderator

    Madhivan is correct.
    you need to separat the bathces by using GO command
    From BOL
    SQL Server applications can send multiple Transact-SQL statements to SQL Server for execution as a batch. The statements in the batch are then compiled into a single execution plan. Programmers executing ad hoc statements in the SQL Server utilities, or building scripts of Transact-SQL statements to run through the SQL Server utilities, use GO to signal the end of a batch.

  4. fractallian New Member

    Awesome, Thanks!!

    I have read about the GO keyword but have never been sure when to use it.

Share This Page