SubQuery Returns more than one value error while inserting the Bulk of data into my DB

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

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.

HTML

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

Posted 3-Sep-12 23:30pm

pranathis012

Updated 3-Sep-12 23:53pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodingLover · Answer 1 · 2012-09-03T23:36:00

Solution 1

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?

Posted 3-Sep-12 23:36pm

CodingLover

Comments

[no name] 4-Sep-12 5:38am

C# only

CodingLover 4-Sep-12 5:40am

Show me the code that you invoke your SP.

[no name] 4-Sep-12 5:41am

i'm getting the error in SP it self

CodingLover 4-Sep-12 5:42am

What is the exact error you get? Could you please post it here.

[no name] 4-Sep-12 5:43am

Msg 512, Level 16, State 1, Procedure lntsp_EMS_PTS_H_Drawing_Schedule, Line 66
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Procedure lntsp_EMS_PTS_H_Drawing_Schedule, Line 71
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Procedure lntsp_EMS_PTS_H_Drawing_Schedule, Line 76
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Procedure lntsp_EMS_PTS_H_Drawing_Schedule, Line 92
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Procedure lntsp_EMS_PTS_H_Drawing_Schedule, Line 96
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Procedure lntsp_EMS_PTS_H_Drawing_Schedule, Line 100
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 515, Level 16, State 2, Procedure lntsp_EMS_PTS_H_Drawing_Schedule, Line 103
Cannot insert the value NULL into column 'Dwg_Size_Code', table 'Engsoft.dbo.PTS_H_Drawing_Schedule'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 512, Level 16, State 1, Procedure lntsp_EMS_PTS_H_Drawing_Schedule, Line 92
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Procedure lntsp_EMS_PTS_H_Drawing_Schedule, Line 96
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Procedure lntsp_EMS_PTS_H_Drawing_Schedule, Line 100
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 515, Level 16, State 2, Procedure lntsp_EMS_PTS_H_Drawing_Schedule, Line 103
Cannot insert the value NULL into column 'Dwg_Size_Code', table 'Engsoft.dbo.PTS_H_Drawing_Schedule'; column does not allow nulls. INSERT fails.
The statement has been terminated.

[no name] 4-Sep-12 5:42am

i checked my SP to insert multiple records at a time that time data is not inserted it shows the error sub query returns more than one value.But when i insert single record means its working fine..

CodingLover 4-Sep-12 5:44am

What about this?

"Cannot insert the value NULL into column 'Dwg_Size_Code', table 'Engsoft.dbo.PTS_H_Drawing_Schedule'; column does not allow nulls. INSERT fails. The statement has been terminated."

Did you check that?

[no name] 4-Sep-12 5:46am

S , i'm passing this 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)

[no name] 4-Sep-12 5:47am

it's working when inserted as a single row but its not working when i insert multiple rows i think pblm is in looping condition only, i don't know that plz help to solve this error

CodingLover 4-Sep-12 5:48am

As I said check that any of your sub-query return more than one value.

CodingLover 4-Sep-12 5:46am

Check any of your sub-query return more that one value as well.