Create PROCEDURE [dbo].[sp_Insert_BulkData] -- Add the parameters for the stored procedure here @InputXML as xml as Begin --Create Temporary Table Declare @temptable TABLE(ID numeric(18,0),ProjectName nvarchar(50));
--Insert Records to Temporary Table from XML string Insert into @temptable(ID,ProjectName) SELECT TempRow.value('(ID)[1]','numeric(18,0)')AS ID,TempRow.value('(Project)[1]','nvarchar(50)')AS Name FROM @InputXML.nodes('/Projects')AS TempTable(TempRow)
select * from @temptable; --Insert into temp Select * from @temptable;
End
By using xml string you can pass bulk of data to sql sp.
vikas227
Member
625 Points
824 Posts
how can we pass dataset to the stored procedure
Feb 19, 2013 06:01 AM|LINK
I need to save the data into multipl tables using single stored procedure and one table will insert the batch of records.
how can i do that please suggest me an idea.
Thanks
Pbalan.in
Contributor
2144 Points
484 Posts
Re: how can we pass dataset to the stored procedure
Feb 19, 2013 06:18 AM|LINK
Hi,
Try this...
--Exec [dbo].[sp_Insert_BulkData] '<Projects><ID>1</ID><Project>Project 1</Project></Projects>
-- <Projects><ID>2</ID><Project>Project 2</Project></Projects>
-- <Projects><ID>3</ID><Project>Project 3</Project></Projects>
-- <Projects><ID>4</ID><Project>Project 4</Project></Projects>'
Create PROCEDURE [dbo].[sp_Insert_BulkData]
-- Add the parameters for the stored procedure here
@InputXML as xml
as
Begin
--Create Temporary Table
Declare @temptable TABLE(ID numeric(18,0),ProjectName nvarchar(50));
--Insert Records to Temporary Table from XML string
Insert into @temptable(ID,ProjectName)
SELECT TempRow.value('(ID)[1]','numeric(18,0)')AS ID,TempRow.value('(Project)[1]','nvarchar(50)')AS Name
FROM @InputXML.nodes('/Projects')AS TempTable(TempRow)
select * from @temptable;
--Insert into temp Select * from @temptable;
End
By using xml string you can pass bulk of data to sql sp.
Mark as answer if it useful
wmec
Contributor
6534 Points
3322 Posts
Re: how can we pass dataset to the stored procedure
Feb 19, 2013 07:03 AM|LINK
You can put the dataset into one temp table instead and also keep one unique group ID for it. Then only pass this unique group ID to other SP.
HuaMin Chen
vikas227
Member
625 Points
824 Posts
Re: how can we pass dataset to the stored procedure
Feb 19, 2013 10:33 AM|LINK
please can you share ur idea with an example,how can i do this.
Thanks
wmec
Contributor
6534 Points
3322 Posts
Re: how can we pass dataset to the stored procedure
Feb 20, 2013 01:20 AM|LINK
1. Have one group ID in the temp table
2. Each time increment this ID
3. Insert the dataset into the temp table using the incremented group ID
HuaMin Chen