SELECT INTO a existing temp table

Posted on 2011-10-29
Last Modified: 2012-05-12
We are writing a stored procedure to perform a 'select into' a temp table. The issue is that the temp table has already been created earlier in the SP. This seems to be causing an error when we try to install the SP. Below is the logic in the SP.

1. create table #sometable (column1 char(15) , column2 char(1) )
2. select column1, '' into #sometable from permtable1 (nolock) where CustomerID = @CustomerID

When we try to install this SP, we get the error below.

Error: There is already an object named '#sometable' in the database.

Is it not possible to perform a bulk operation into an existing temporary table?


Question by:dthansen
    LVL 33

    Expert Comment

    insert into #sometable
    select column1, ''
    from permtable1 (nolock)
    where CustomerID = @CustomerID

    Author Comment

    We were attempting to take advantage of minimal logging to improve performance.

    The 'insert into' will use full logging.

    LVL 25

    Expert Comment

    Why are you using a temp table?

    The best performance-tuning of an insert is to avoid it. With creating a permanent view you can avoid many temp tables that are use for simplification of further sql commands (so no updating/deleting).    A view also takes advantage indexes on the original tables.

    If performance is the issue also see at the advantage of table variables (less recompilations) or create indexes on your temp tables (more efficient when often used after creation) .

    About your sql. You get the error because you have first a create #table and then use the syntax to create  a table in the insert-statement. Creating the #table in the select is easy because you don't have to think about the create statement, but it locks the system tables in tempdb for the whole duration of the insert. If you want to avoid logging because off performance I guess that it is about a lot of records. This locking will delay other processes that want to use tempdb and if it's a common practice even can trigger deadlocks in your tempdb (fun to find).
    LVL 23

    Expert Comment

    by:Racim BOUDJAKDJI
    <<We were attempting to take advantage of minimal logging to improve performance.>>
    Reducing logging has nothing to do with using temp tables.  You can reduce logging volume only through using BULK INSERT type of inserts.  Besides, using temp tables actually simply actually increases logging since the system logs both the insert into the temp table to the final target table logging.

    I wander here people get the idea that using temp tables will somehow improve performance.
    LVL 11

    Accepted Solution

    When you use select into automatically a temp table is created. You mustn't create the table in your stored procedure if you perform a select into command.
    LVL 25

    Expert Comment

    Ok the points are not mine, although I said the same in other words, but mind my other comment on locking.  

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now