Simple Query problem - QUery Analyser operation

• sqlguy7777

  Right there with Babe

  Points: 720

  More actions

  August 10, 2003 at 9:48 pm

  #162122

  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.

• mccork

  SSC-Insane

  Points: 22288

  More actions

  August 10, 2003 at 10:26 pm

  #469323

  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