August 10, 2003 at 9:48 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 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...
SG.
August 10, 2003 at 10:26 pm
I can't replicate the error. I don't have tbl_disk_SQL1, so I tried with Authors from Pubs. ie:
select * into #drive from pubs..authors
alter table #drive add [ID] INT IDENTITY (1,1)
alter table #drive add drive_total INT
select * from #drive
No errors were returned, and the final select showed the populated ID and null drive_total fields at the end of each row. What error/s are you getting?
BTW. Another way you can achieve much the same, without ALTERs, is:
select ID=IDENTITY(INT,1,1), *, drive_total=CAST(0 AS INT)
into #drive from pubs..authors
select * from #drive
Cheers,
- Mark
Edited by - mccork on 08/10/2003 10:37:43 PM
Cheers,
- Mark
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply