August 10, 2003 at 9:47 pm
Howdy,
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 from the newly modified table.
All goes well if I only do the select into then modify the table, 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...
SG.
August 10, 2003 at 10:12 pm
Hi there
Your example works fine for me.
Can you post your instance version info? have a look through MSDN as well. Ive tried wrappering around different combos of begin end, trans and cant repeat it.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
August 11, 2003 at 6:03 pm
Are you actually running the code or just getting a plan?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply