locked
Temp table value problem RRS feed

  • Question

  • I created temp table like the below, select into the values, however, I got the error says"There is already an object named '##report' in the database.".  Please help

    Create table ##report
    (
    OMSC# int,
    Total_Received int,
    Total_Action int,
    Monthly varchar(10),
    Percentage decimal
    )
    select *
    into ##report
    from Requests

    Monday, March 23, 2015 7:04 AM

Answers

  • --Option.1 --> Use INSERT INTO if you already have created table in advance.
    Create table ##report
    (
    OMSC# int,
    Total_Received int,
    Total_Action int,
    Monthly varchar(10),
    Percentage decimal
    )
    
    INSERT INTO ##report
    SELECT * FROM Requests
    
    -- Option.2 --> Directly create ##report temp table by using SEELCT INTO
    
    SELECT *
    INTO ##report
    FROM Requests


    Cheers,
    Vaibhav Chaudhari
    [MCTS], [MCP]

    • Marked as answer by sdnd2000 Monday, March 23, 2015 8:28 AM
    Monday, March 23, 2015 7:08 AM
  • In addition to Vaibhav's comment,##report is GLOBAL temp table (##), the scope is global. Hence, if other session is already created, then the current session tried to create it, it will show the error message. 

    If you are looking for session scope temp table, you may use the below:(note that only one #)

    Create table #report
    (
    OMSC# int,
    Total_Received int,
    Total_Action int,
    Monthly varchar(10),
    Percentage decimal
    )
    select *
    into #report
    from Requests


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]


    • Edited by SQLZealots Monday, March 23, 2015 7:10 AM
    • Proposed as answer by Jackson_1990 Monday, March 23, 2015 8:13 AM
    • Marked as answer by sdnd2000 Monday, March 23, 2015 8:28 AM
    Monday, March 23, 2015 7:09 AM
  • IF OBJECT_ID('tempdb..##report') IS NOT NULL
    DROP TABLE ##report
    
    
    Create table ##report
    (
    OMSC# int,
    Total_Received int,
    Total_Action int,
    Monthly varchar(10),
    Percentage decimal
    )
    select *
    --into ##report
    from Requests


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by sdnd2000 Monday, March 23, 2015 8:28 AM
    Monday, March 23, 2015 8:06 AM

All replies

  • --Option.1 --> Use INSERT INTO if you already have created table in advance.
    Create table ##report
    (
    OMSC# int,
    Total_Received int,
    Total_Action int,
    Monthly varchar(10),
    Percentage decimal
    )
    
    INSERT INTO ##report
    SELECT * FROM Requests
    
    -- Option.2 --> Directly create ##report temp table by using SEELCT INTO
    
    SELECT *
    INTO ##report
    FROM Requests


    Cheers,
    Vaibhav Chaudhari
    [MCTS], [MCP]

    • Marked as answer by sdnd2000 Monday, March 23, 2015 8:28 AM
    Monday, March 23, 2015 7:08 AM
  • In addition to Vaibhav's comment,##report is GLOBAL temp table (##), the scope is global. Hence, if other session is already created, then the current session tried to create it, it will show the error message. 

    If you are looking for session scope temp table, you may use the below:(note that only one #)

    Create table #report
    (
    OMSC# int,
    Total_Received int,
    Total_Action int,
    Monthly varchar(10),
    Percentage decimal
    )
    select *
    into #report
    from Requests


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]


    • Edited by SQLZealots Monday, March 23, 2015 7:10 AM
    • Proposed as answer by Jackson_1990 Monday, March 23, 2015 8:13 AM
    • Marked as answer by sdnd2000 Monday, March 23, 2015 8:28 AM
    Monday, March 23, 2015 7:09 AM
  • IF OBJECT_ID('tempdb..##report') IS NOT NULL
    DROP TABLE ##report
    
    
    Create table ##report
    (
    OMSC# int,
    Total_Received int,
    Total_Action int,
    Monthly varchar(10),
    Percentage decimal
    )
    select *
    --into ##report
    from Requests


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by sdnd2000 Monday, March 23, 2015 8:28 AM
    Monday, March 23, 2015 8:06 AM