Post reply

Temp Table in SQL2K5

  • June 5, 2007 at 12:31 am

    #176968

    Hi All,

    While migrating from SQL 2000 to SQL 2005, observed an issue with temp table.

    In SQL 2000, while populating the temp table, it is not mandatory to specify all the columns.

    #Temp1 has four columns

    OID

    ODATE

    OOWNER

    OSTATUS

    If use like below

    INSERT INTO Temp1 from Select O_ID,O_DATE,O_OWNER From OData

    This query was working fine with SQL 2000 but throws error with SQL 2005.

    Is this new behavior change of SQL 2005 or something else required?

    Thanks in advnace.

  • June 5, 2007 at 12:40 am

    #710791

    Your insert statement itself seems to be wrong. It should be

     

    insert into #temp select col1,..coln from tablename.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • June 5, 2007 at 1:02 am

    #710793

    INSERT INTO #Temp1 (OID, ODATE, OOWNER)

    SELECT O_ID,O_DATE,O_OWNER

    FROM OData

  • June 5, 2007 at 1:20 am

    #710798

    Thanks for your reply and about the query it was typo mistake.

    The original question was to get an idea on the behavior change as this query was running fine with SQL 2000 but with SQL 2005 i had changed with completed select list.

    I have found some of T-SQL statement differences between 2K and 2K5 and didn't get anything on this behavior on temp table and the question was to understand this change.

    Thanks

  • June 7, 2007 at 4:42 pm

    #711548

    You said the query was throwing an error... probably be helpful if you posted the error message(s).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply