子查询将多个数据插入我的数据库时返回多个值错误 [英] SubQuery Returns more than one value error while inserting the Bulk of data into my DB

问题描述

我想将大量记录插入到我的数据库中.
为此，我创建了SP，但是它只插入了一条记录.当我插入大量记录时，它不起作用，它表明错误子查询返回了多个值. @Dwg_Size_Code，@Disc_Code，@ Dwg_Category_Code读取所有值.

I want to INSERT Bulk of records into my DB.
For that i create SP ,but in that it''s inserting only one record.When i insert Bulk of records,it''s not working it shows the error subquery returns more than one value .Can any one help me looping condition of @Dwg_Size_Code , @Disc_Code ,@Dwg_Category_Code to read all the values.

/*    
  */    
    
ALTER  PROCEDURE lntsp_EMS_PTS_H_Drawing_Schedule    
    
 @XMLItemsDoc xml ,
 @User_Id INT,  
 @error VARCHAR(100)=NULL OUTPUT   
    
    
AS                  
     
BEGIN                  
    
 SET NOCOUNT ON                    
    
 DECLARE @XML_Hndl int,@xPath Varchar(200)      
    
  
 set @xPath='/NewDataSet/Table1'      
    
 exec sp_xml_preparedocument @XML_Hndl OUTPUT,@XMLItemsDoc         
    
    
  select Title,Dwg_Size_Desc,Disc_Short_Desc,Dwg_Category_Desc,Approval_Date,Release_Date,Multi_Disc_Check,Proj_Code,
  Doc_No,SNo,Client_Approval_Date,Client_Release_Date,Weightage    
    
  into #tempUplodItems       
  FROM  OPENXML(@XML_Hndl,@xPath,2)        
  with(Title VARCHAR(100),Dwg_Size_Desc VARCHAR(15),Disc_Short_Desc VARCHAR(15),Dwg_Category_Desc VARCHAR(15),Approval_Date DATETIME,Release_Date DATETIME,Multi_Disc_Check BIT,Proj_Code VARCHAR(15),
  Doc_No VARCHAR(50),SNo INT,Client_Approval_Date DATETIME,Client_Release_Date DATETIME,Weightage FLOAT)      
    
  exec  sp_xml_removedocument   @XML_Hndl
                
     
   
    
   
   DECLARE @Dwg_Size_Code VARCHAR(15)=( SELECT A.Dwg_Size_Code FROM PTS_M_Drawing_Size A, #tempUplodItems X
										WHERE X.Dwg_Size_Desc=A.Dwg_Size_Desc)
   SELECT @Dwg_Size_Code
   
   DECLARE @Disc_Code VARCHAR(15)=(SELECT A.Disc_Code FROM EMS_M_Discipline A,#tempUplodItems X
								   WHERE X.Disc_Short_Desc=A.Disc_Short_Desc)
   SELECT @Disc_Code
   
   DECLARE @Dwg_Category_Code VARCHAR(15)=(SELECT A.Dwg_Category_Code FROM PTS_M_Drawing_Category A,#tempUplodItems X
											WHERE X.Dwg_Category_Desc=A.Dwg_Category_Desc)
   SELECT @Dwg_Category_Code
  
 INSERT INTO dbo.PTS_H_Drawing_Schedule      
 (    
 Title, Dwg_Size_Code, Disc_code, Dwg_Category_Code, Approval_Date, Release_Date, Active_Tag, 
 Created_By, Created_On, Modified_By, Modified_On, Sch_Rev_No, Sch_Rev_Cause, Sch_Rev_Reason, 
 Multi_Disc_Check, Proj_Code, Client_Category, Site_Category, Section_Code, Doc_No, SNo, 
 Sheet_No, DDH_Approval, Approval_Tag, Doc_Description, No_Of_Drawing, Client_Doc_No, Client_Approval_Date, 
 Client_Release_Date, Weightage, Man_Hours, Deactivation_remark
 )    
 SELECT Title,@Dwg_Size_Code,@Disc_Code,@Dwg_Category_Code,Approval_Date,Release_Date,'Y',@User_Id,GETDATE(),@User_Id,GETDATE(),0,0,'Dont Know',Multi_Disc_Check,Proj_Code,
 'DWG000002','DWG000001','SEC000923',Doc_No,SNo,0,0,'Y','Drg/Doc',0,'KAPP - 3&4/51300/2501/DD',Client_Approval_Date,Client_Release_Date,Weightage,5,'No comments'    
 from #tempUplodItems    
 
   
     
 END

预先感谢

Thanks in advance

推荐答案

SP一次插入一组数据.要多次插入，必须每次都定义参数值并调用SP.您是使用C#还是什么?

SP insert one set of data at a time. To insert multiple times you have to define parameter values each time and invoke the SP. You doing this with C# or what?

这篇关于子查询将多个数据插入我的数据库时返回多个值错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！