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
– booyaa, Commented Jan 15, 2013 at 8:59
you don't need to drop temp tables at the end of a procedure. — Filip De Vos
– Filip De Vos, Commented Jan 15, 2013 at 9:23

Sam Roberts · Accepted Answer · 2013-07-17 16:12:02Z

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, 2013 at 16:12

Sam Roberts

24.2k1 gold badge43 silver badges65 bronze badges

answered Jan 15, 2013 at 8:56

Dave K

2,0051 gold badge12 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ivan Over a year ago

Hi Dave i have to do row by row operations with the help of the table......plz suggest me some idea...

Dave K Over a year ago

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.

Ivan Over a year ago

thank u dave i understood....... but after i select the values from the temporary table it does not show any value.........

Dave K Over a year ago

Doesn't show any rows? Or doesn't show any value for your ID column?

Dave K Over a year ago

Scrap my previous comment. SELECT IDENTITY() only works when using SELECT INTO syntax. So remove the CREATE TABLE statements and just use SELECT INTO.

|

Collectives™ on Stack Overflow

SQL Server stored procedure creating temp table and inserting value

1 Answer 1

7 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Linked

Related