Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    220

    Angry Unanswered: SQL Query - Query Optimiser problem?

    Howdy,

    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!!


    Thanks in advance...

    SG.

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    What version of SQL are you using ... and what is ur OS platform.

  3. #3
    Join Date
    Jul 2003
    Location
    London
    Posts
    26

    Re: SQL Query - Query Optimiser problem?

    Hi

    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.

    Regards

    Richard...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •