This is driving me nuts......when I write a query (in Query Analyser ) to perform a "select into" all goes well. Then I decide I want to modify the table by adding columns to the table , all in the same chunk of code. Then I want to select values from the modified table. should be straight forward , right? Wrong.....*sigh*
All goes well if I (1) select into then (2) modify the table, only. ( i.e. use a "GO" at the end of (10 & (2). )
But if I do (1) select into, (2) modify then (3) do a select from the newly modifed table- it wont actually add the columns to the table.The select at the end seems to trip it up somehow.
I have tried using serialised transactions, begin & end statements, stored procedures etc etc - no joy.
I suspect its something to do with the TSQL query optimiser perhaps getting ahead of itself and getting under its own feet ( so to speak ).
Is it a Query Optimiser problem or something else?
I am VERY frustrated!
The code :
(1) select * into #drive from tbl_disk_SQL1
(2) alter table #drive add [ID] INT IDENTITY (1,1)
alter table #drive add drive_total INT
(3) select * from #drive
Any suggestions welcome!!
Just tried the following on SQL 7 (SP4) and SQL 2000 (SP2)
SELECT * INTO #temp1 FROM authors
ALTER TABLE #temp1 ADD id int IDENTITY(1,1)
ALTER TABLE #temp1 ADD total int
SELECT * FROM #temp1
This works fine.
My first thought was that the optimizer first past through was just replacing the * with the field names and ignoring the alter table statement, so when it does the actual queries it was just using the field list it first work out. To prove that just replace your SELECT * FROM #drive with SELECT fieldlist (including the new fields) FROM #drive.