SQL Server stored procedure creating temp table and inserting value
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.
Comments
-
Ivan almost 4 years
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
-
Ivan over 11 yearsHi Dave i have to do row by row operations with the help of the table......plz suggest me some idea...
-
Dave K over 11 yearsInstead 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 11 yearsthank u dave i understood....... but after i select the values from the temporary table it does not show any value.........
-
Dave K over 11 yearsDoesn't show any rows? Or doesn't show any value for your ID column?
-
Dave K over 11 yearsScrap my previous comment. SELECT IDENTITY() only works when using SELECT INTO syntax. So remove the CREATE TABLE statements and just use SELECT INTO.
-
Stefan Steiger almost 11 yearsSYNTAX: SELECT * INTO #newtable FROM YOUR_TABLE;
-
Justin about 5 yearsBe 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 stackoverflow.com/questions/1302670/… for more details.