Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 844
  • Last Modified:

SELECT INTO a existing temp table

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?


1 Solution
insert into #sometable
select column1, ''
from permtable1 (nolock)
where CustomerID = @CustomerID
dthansenAuthor Commented:
We were attempting to take advantage of minimal logging to improve performance.

The 'insert into' will use full logging.

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).
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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.
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.
Ok the points are not mine, although I said the same in other words, but mind my other comment on locking.  

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now