This is driving me nuts......when I write a query ( In Query Analyser ) to perform a select into ( to load data & create a table at the same time ) all goes well. Then I decide I want to alter the table by adding columns in the same chunk of code. Then I want to select values from the modified table.
All goes well if I select into then modify, the table only, but not if I select into, modify then select again. I have tried using serialised transactions, begin & end statements, stored procedures - no joy.
I suspect its something to do with the TSQL query optimiser perhaps getting ahead of itself. Is there a certain sequence the query optimiser does things & if so where can I find this info?
The code :
select * into #drive from tbl_disk_SQL1
alter table #drive add [ID] INT IDENTITY (1,1)
alter table #drive add drive_total INT
select * from #drive
Any suggestions welcome!!
Thanks in advance...