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
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.
Source: Stackoverflow.com