none
SELECT INTO TEMP TABLE

    Question

  • The following batch does not compile. It works for real tables but not temp tables. I need to get this to work. Any ideas? Thanks.

    code
    -------------------
    IF 1 = 1 --IF CONDITION
    BEGIN
    SELECT * INTO #TEMP FROM TABLE1
    END
    ELSE
    BEGIN
    SELECT * INTO #TEMP FROM TABLE2
    END
    --------------------
    Error Msg
    Msg 2714, Level 16, State 1, Line 7
    There is already an object named '#TEMP' in the database.
    --------------------

    Tuesday, April 17, 2007 10:18 AM

Answers

  • you are rightly mentioned the BOL Refrence that you can not have same temporary table name in a batch. So droping is not an option. 

     

    Refer this link http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1.

     

    You can have different Temptable or table variable. I don't thing it will have any impact as far as performance is concerned. Otherwise, you should create the table outside the loop and alter the table inside the loop as u need.

     

    Madhu

    Tuesday, April 17, 2007 12:48 PM
    Moderator

All replies

  • Since you are using the loop the #TEMP table is created on first loop itself..

    So when the second loop try to execute the INTO command it fails, bcs the #TEMP table is aleary exist...

     

    Solution:

    Drop your #TEMP table before doing the INTO ...

     

    example:

     

    Code Snippet

    IF 1 = 1 --LOOP CONDITION

        BEGIN

             If Exists(Select * from tempdb..sysobjects Where id = object_id('tempdb.dbo.#temp'))

             DROP TABLE #TEMP

            SELECT * INTO #TEMP FROM TABLE1

        END

    ELSE

        BEGIN

             If Exists(Select * from tempdb..sysobjects Where id = object_id('tempdb.dbo.#temp'))

             DROP TABLE #TEMP

            SELECT * INTO #TEMP FROM TABLE2

        END

     

    or

     

    You can use INTO on first loop and rest you can use simple insert statement

     

    Code Snippet

    IF 1 = 1 --LOOP CONDITION

        BEGIN

             If NOT Exists(Select * from tempdb..sysobjects Where id = object_id('tempdb.dbo.#temp'))

                      SELECT * INTO #TEMP FROM TABLE1

              Else

                      Insert Into #TEMP

                      Select * from TABLE1

        END

    ELSE

        BEGIN

             If NOT Exists(Select * from tempdb..sysobjects Where id = object_id('tempdb.dbo.#temp'))

                      SELECT * INTO #TEMP FROM TABLE2

              Else

                      Insert Into #TEMP

                      Select * from TABLE2

        END

     

     

    Tuesday, April 17, 2007 10:34 AM
  • Hello Mannivannan,

    Thank you so much for your reply.

    Sorry, there was a slight mistake in my code example.  It's just an IF-CONDITION

    code
    ---------------------------------------------------------
    IF 1 = 1 --IF CONDITION
        BEGIN
            SELECT * INTO #TEMP FROM TABLE1
        END
    ELSE
        BEGIN
            SELECT * INTO #TEMP FROM TABLE2
        END
    ---------------------------------------------------------
    I read the following from MSDN books online: "If more than one temporary table is created inside a single stored procedure or batch, they must have different names."  I assume that means that the batch above is invalid, even though the create #temp stmts are separated with a mutually exclusive IF-condition.

    Any ideas?.  Thanks again for your response.
    Tuesday, April 17, 2007 11:44 AM
  • Yes You are partially correct.

    Your case since it is a if statement any one block will be executed..and your code is 99.99% perfect.

     

    May be you executed twice the statements(i hope the sample code is not enclosed in the SP).

    It always better to check the table is already there are not. If there then you can drop and recreate it ..

     

    ex,

    Code Snippet

    If Exists(Select * from tempdb..sysobjects Where id = object_id('tempdb.dbo.#temp'))

             DROP TABLE #TEMP

     

    Tuesday, April 17, 2007 12:04 PM
  • Thanks again for your response.

    Here is a compilable code snippet that illustrates the problem I'm facing
    ---------------------------------------------------------
    IF EXISTS(SELECT * FROM TEMPDB.SYS.TABLES WHERE NAME LIKE '#TEMP%')
    DROP TABLE #TEMP

    IF 1=0 --[IF CONDITION]
    BEGIN
    CREATE TABLE #TEMP
    (
    ID INT
    )
    END
    ELSE
    BEGIN
    CREATE TABLE #TEMP
    (
    NAME VARCHAR(10)
    )
    END
    ---------------------------------------------------------
    If you run this code snippet, you will get the following error
    ------------------------------------------------------------
    Msg 2714, Level 16, State 1, Line 14
    There is already an object named '#TEMP' in the database.


    However, if you were to change the temp table to a REAL table, it would execute successfully.

    I read the following from MSDN books online: "If more than one temporary table is created inside a single stored procedure or batch, they must have different names." I assume that means that the batch above is invalid, even though the create #temp stmts are separated with a mutually exclusive IF-condition.


    Any ideas?

    Thanks again for your response.
    • Proposed as answer by TomPester Thursday, March 03, 2011 9:57 AM
    Tuesday, April 17, 2007 12:18 PM
  • Yes. On compile time itself it gives an error. You have to name it differently..

    Tuesday, April 17, 2007 12:29 PM
  • you are rightly mentioned the BOL Refrence that you can not have same temporary table name in a batch. So droping is not an option. 

     

    Refer this link http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1.

     

    You can have different Temptable or table variable. I don't thing it will have any impact as far as performance is concerned. Otherwise, you should create the table outside the loop and alter the table inside the loop as u need.

     

    Madhu

    Tuesday, April 17, 2007 12:48 PM
    Moderator
  • SELECT INTO creates a temp table....so u cant CREATE one temp table twice...(in a batch...)

    if u want this to work , use create table to create the temp table ONCE then use it in  IF..ELSE block as

    INSERT INTO #temptbl select * from......

     

     

    Tuesday, April 17, 2007 12:58 PM

  • You could Define the #Temp table BEFORE the IF condition, and then just INSERT into the #Temp table. (Of course, you would have to do a bit more work, like define the table in advance...)


    Then there would not be an error on the branchs.

    Tuesday, April 17, 2007 2:55 PM
    Moderator
  • If the schema of TABLE1 and TABLE2 are the same then you can do below:
     
    Code Snippet
    SELECT *
    INTO #TEMP
    FROM TABLE1
    WHERE <condition>
    UNION ALL
    SELECT *
    FROM TABLE2
    WHERE not( <condition> )

     

     
    Tuesday, April 17, 2007 7:53 PM
  • Try this

    IF EXISTS(SELECT * FROM TEMPDB.SYS.TABLES WHERE NAME LIKE '##TEMP%')

    DROP TABLE ##TEMP

    Declare @testStr nvarchar(400)

    SET @testStr = NULL

    IF 1=0 --[IF CONDITION]

    BEGIN

    SET @testStr = N'CREATE TABLE ##TEMP ( ID INT )'

    END

    ELSE

    BEGIN

    SET @testStr = N' CREATE TABLE ##TEMP ( NAME VARCHAR(10) ) '

    END

    EXEC sp_executeSQL @testStr

    select * from ##temp

    Wednesday, April 18, 2007 7:28 AM
  • I know this post is really old, but what if you cannot destroy the table (not the #temp, an actual table)??

    I get the same error, but cannot destroy the "export" table.

    Any suggestions???
    Saturday, November 21, 2009 9:14 AM
  • start with two temp tables and rename using a conditional :

    IF 1 = 1 --IF CONDITION
    BEGIN
    SELECT * INTO #TEMP FROM TABLE1;
    END
    ELSE
    BEGIN
    SELECT * INTO #TEMP2 FROM TABLE2;
    END

                IF NOT ( OBJECT_ID('YourDB.dbo.[#TEMP2]', 'U') IS NULL )
                    BEGIN
                        EXEC sp_rename
                            '#TEMP2'
                          , '#TEMP';
                    END

    SELECT * FROM #TEMP;

    Wednesday, March 30, 2011 12:10 AM
  • Aren't #Temp tables written to disk in TempTB where the @Temp table variables are created/stored in memory? In the past I've seen a huge performance disparity between the two. Plus the table variable lets others following the code see the values/dataTypes being inserted. Add in the added complexity of being required to drop and recreate the table and it just seems cleaner to use the @VariableTable unless there is genuinely a need to make the data persist for the SQL session.
    Wednesday, August 10, 2011 3:45 PM