数据库中已经有一个名为“pdbcompr”的对象 [英] There is already an object named 'pdbcompr' in the database

问题描述

我正在使用SQL Server 2008.我想将临时表中的数据插入数据库。我正在使用While循环将数据从临时表插入数据库表。现在我面临一个问题：对象已经存在于数据库中。请帮我找一个合适的解决方案。提前致谢

I am using SQL server 2008. I want to insert data from a temp table to database. I am using While loop to insert data from temp table to database table. Now I am facing an issue: object already exist in the database. Please help me to find a proper solution. Thanks in advance

declare @rev as int  ,
    @sQuotationNo NVARCHAR(15),                                                                                      
    @sQRevNo int                                                                                               
    set @rev=(select top 1 QRevNo from PDBCompr Where QuotationNo='JCS_G1415_008' and QRevNo<>'3' order by QRevNo desc)   

    ;with cte as   
    (  
    SELECT    
     ROW_NUMBER() OVER(ORDER BY QuotationNo) AS sSLNO,                                                 
     [CompanyCode] ,      
     [ProjectCode] ,   
     [PRevNo],  
     [CSlNo],      
     [ComprDescription] ,      
     [PID] ,      
     [RatingCode] ,      
     [Rating] ,      
     [StdSystems] ,      
     [BoosterSystems] ,      
     [GCUSystems] ,      
     [KOFSystems] ,      
     [HeaterSystems] ,      
     [OtherSystems] ,      
     [Comments] ,      
     [Currency1] ,      
     [UnitPrice1] ,      
     [Currency2] ,      
     [ExchRate2] ,      
     [UnitPrice2] ,      
     [Currency3],      
     [ExchRate3] ,      
     [UnitPrice3] ,      
     [CreateId] ,      
     [CreateDate] ,      
     [UpdateId] ,      
     [UpdateDate]                                                                                          
    from PDBCompr         
    where QuotationNo='JCS_G1415_008' and CompanyCode ='001' and QRevNo ='2'               
    and AddCmprId not in (select distinct AddCmprId from PDBCompr where QuotationNo='JCS_G1415_008' and CompanyCode ='001' and QRevNo ='3'   )  
    )      

    select * into #temp from cte   

    declare @cnt int , @loopCnt int=1 
    select @cnt =( select COUNT(*) from #temp)  

     while (@loopCnt<=@cnt)
     begin

    ;with cte2 as 
    (
     SELECT    
     sSLNO,                                                 
     [CompanyCode] ,      
     [ProjectCode] ,           
     (select Max(PRevNo)+1 from PDBCompr) [PRevNo],      
     (select Max(CSlNo) +1 from PDBCompr)[CSlNo],      
     [ComprDescription] ,      
     [PID] ,      
     [RatingCode] ,      
     [Rating] ,      
     [StdSystems] ,      
     [BoosterSystems] ,      
     [GCUSystems] ,      
     [KOFSystems] ,      
     [HeaterSystems] ,      
     [OtherSystems] ,      
     [Comments] ,      
     [Currency1] ,      
     [UnitPrice1] ,      
     [Currency2] ,      
     [ExchRate2] ,      
     [UnitPrice2] ,      
     [Currency3],      
     [ExchRate3] ,      
     [UnitPrice3] ,      
     [CreateId] ,      
     [CreateDate] ,      
     [UpdateId] ,      
     [UpdateDate]                                                                                       
      from #temp where sSLNO=@loopCnt
      )      

    select * into PDBCompr from cte2
    set @loopCnt= @loopCnt+1
    drop table  #temp
      end

我试过的：



我试图删除临时表

What I have tried:

I tried to drop the temp table

推荐答案

选择*从cte2进入PDBCompr



在这一行你再次制作一个表PDBCompr，但是在上面的代码中你从这个表中选择记录就意味着它已经存在。



所以如果你想在这个表中插入记录，那么你需要改变你的查询，如下所示：

插入PDBCompr select * from cte2





Ashish

select * into PDBCompr from cte2

in this line you are again making a table "PDBCompr", but in above code you are selecting records from this table it means its already exist.

so if you want to insert record in this table then you need to change your query like below:
insert into PDBCompr select * from cte2


Ashish

这篇关于数据库中已经有一个名为“pdbcompr”的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！