SQL Server stored procedure creating temp table and inserting value

Question

I am trying to select values from different table and inset it in to the temporary table.

I need a identity field in the temporary table. When I try to execute the following code it throws an error:

*Msg 2714, Level 16, State 1, Procedure SelectCashDetails, Line 27
There is already an object named '#ivmy_cash_temp1' in the database.*

I try to change the temp table into different names even after it throws the same error.

This is my code:

ALTER PROCEDURE [dbo].[SelectCashDetails] 
(
   @trustcompanyid BigInt,
   @trustaccountid BigInt,
   @planid BigInt,
   @fromdate varchar(20),
   @todate varchar(20),
   @movetype varchar(20),
   @typedesc varchar(20)
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    CREATE TABLE #ivmy_cash_temp1          
     ( tmovedate datetime,          
       tmovedesc varchar(20),    
       tmoneymovetype varchar(20),
       tplanbal decimal(18,6),         
       tsourcetype BigInt,           
       tdestinationtype BigInt) 

   SELECT 
       IDENTITY(int) AS id, 
       CMM.movedate,
       CDCP.paramdesc,
       CMM.movementtypecd,
       CMM.amountmoved,
       CMM.planbalance,
       cmm.sourceaccounttypeid,
       cmm.destinationaccounttypeid 
   into #ivmy_cash_temp1  
   from 
       cash_moneymove CMM 
   inner join 
       CDC_PARAMETERS CDCP on CMM.movedescriptioncd=CDCP.paramcd 
   where 
       CMM.movedescriptioncd = @typedesc 
       and PARAMNAME = 'Cash AccountType Desc'

   select * from #ivmy_cash_temp1
END

Don't you need to drop temporary tables after use? i.e. at the end of the stored procedure? — booyaa, Jan 15 '13 at 08:59
you don't need to drop temp tables at the end of a procedure. — Filip De Vos, Jan 15 '13 at 09:23

score 8 · Accepted Answer · edited Jul 17 '13 at 16:12

8

A SELECT INTO statement creates the table for you. There is no need for the CREATE TABLE statement before hand.

What is happening is that you create #ivmy_cash_temp1 in your CREATE statement, then the DB tries to create it for you when you do a SELECT INTO. This causes an error as it is trying to create a table that you have already created.

Either eliminate the CREATE TABLE statement or alter your query that fills it to use INSERT INTO SELECT format.

If you need a unique ID added to your new row then it's best to use SELECT INTO... since IDENTITY() only works with this syntax.

edited Jul 17 '13 at 16:12

Sam Roberts

23,951
1
40
64

answered Jan 15 '13 at 08:56

Dave K

1,845
1
11
9

Hi Dave i have to do row by row operations with the help of the table......plz suggest me some idea... – Ivan Jan 15 '13 at 08:59
Instead of doing SELECT IDENTITY( int ) AS id,CMM.movedate,.... INTO #ivmy_cash_temp1 etc... to fill your table with data. Use INSERT INTO #ivmy_cash_temp1 SELECT IDENTITY( int ) AS id,CMM.movedate,.... FROM etc. – Dave K Jan 15 '13 at 09:00
thank u dave i understood....... but after i select the values from the temporary table it does not show any value......... – Ivan Jan 15 '13 at 09:02
Doesn't show any rows? Or doesn't show any value for your ID column? – Dave K Jan 15 '13 at 09:06
1

Scrap my previous comment. SELECT IDENTITY() only works when using SELECT INTO syntax. So remove the CREATE TABLE statements and just use SELECT INTO. – Dave K Jan 15 '13 at 09:08
SYNTAX: SELECT * INTO #newtable FROM YOUR_TABLE; – Stefan Steiger Aug 08 '13 at 16:38
Be cautious of using select * into #temptable from YOUR_TABLE in production, depending on the amount of data being inserted or how long the YOUR_TABLE query takes (this could be a view or a query too). the schema will be locked the entire duration of this query. this can block SSMS and certain other queries. see https://stackoverflow.com/questions/1302670/sql-server-select-into-and-blocking-with-temp-tables for more details. – Justin May 10 '19 at 14:51

SQL Server stored procedure creating temp table and inserting value

1 Answers1