SQL Server存储过程创建临时表和插入值

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.*

*消息2714,级别16,状态1,过程SelectCashDetails,第27行数据库中已存在名为“#ivmy_cash_temp1”的对象。*

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

1 个解决方案

#1

A SELECT INTO statement creates the table for you. There is no need for the CREATE TABLE statement before hand.

SELECT INTO语句为您创建表。事先不需要CREATE TABLE语句。

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.

发生的事情是您在CREATE语句中创建#ivmy_cash_temp1,然后当您执行SELECT INTO时,DB会尝试为您创建它。这会导致错误,因为它正在尝试创建您已创建的表。

Either eliminate the CREATE TABLE statement or alter your query that fills it to use INSERT INTO SELECT format.

消除CREATE TABLE语句或更改填充它的查询以使用INSERT INTO SELECT格式。

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.

如果您需要在新行中添加唯一ID,则最好使用SELECT INTO ...因为IDENTITY()仅适用于此语法。

#1