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

User · Accepted Answer

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

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

Examples related to sql-server

Examples related to sql-server-2005

Examples related to stored-procedures