SQL Server stored procedure creating a temporary table and insert value

Question

I am trying to select values ​​from another table and paste it into a temporary table.

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

* Msg 2714, level 16, state 1, SelectCashDetails procedure, line 27
There is already an object in the database with the name "# ivmy_cash_temp1". *

I try to change the temporary table to 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 

Answer 1

The A SELECT INTO creates a table for you. There is no need for a CREATE TABLE statement before starting work.

What happens is that you create #ivmy_cash_temp1 in your CREATE statement, then the database tries to create it for you when you execute SELECT INTO . This causes an error because it is trying to create an already created table.

Either remove the CREATE TABLE statement, or modify your query that populates it to use the INSERT INTO SELECT format.

If you need a unique identifier added to your new line, it is better to use SELECT INTO ... since IDENTITY() only works with this syntax.