[sql-server] SQL Server stored procedure creating temp table and inserting value

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

This question is related to sql-server sql-server-2005 stored-procedures

The answer is


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.


Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?

Examples related to stored-procedures

How to create temp table using Create statement in SQL Server? How do I pass a list as a parameter in a stored procedure? SQL Server IF EXISTS THEN 1 ELSE 2 Stored procedure with default parameters Could not find server 'server name' in sys.servers. SQL Server 2014 How to kill all active and inactive oracle sessions for user EXEC sp_executesql with multiple parameters MySQL stored procedure return value SQL Server: use CASE with LIKE SQL server stored procedure return a table