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