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?
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
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.
