I am going to insert Multiple record IN sql using storedProcedure from gridview...
for this purpose i am using cell concatinating method and send whole gridview cell data in string variable.
i have tried xml document method or sending data in table through looping as well
.....but didn't satisfy from both of them... b/c concatinating method required a delimeter for seperation(for which i have restrict the user from that sepereator ) and xml method required an extra file which is not a efficient approach ..
Send all the Data using delimeter seperated format..
suppose, u wants to use , as delimeter, concatenate the string using that.. and send that string to back end..
in back-end, divide that string and insert it.
Below function will divide a comma seperated string into multiple values..
syntax: select * from fn_split(@yourstring, 'your delimiter')
function:
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[fn_split] Script Date: 02/15/2010 19:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_split](@arr AS NVARCHAR(2000), @sep AS NCHAR(1))
RETURNS TABLE
RETURN(
WITH Numbers
AS
(
SELECT TOP 2000
ROW_NUMBER() OVER(ORDER BY sc1.NAME) AS N
FROM Master.dbo.SysColumns sc1
)
SELECT
SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n) AS element,row_number() over (order by (select null)) as pos
FROM Numbers
WHERE
n <= LEN(@arr) + 1
AND SUBSTRING(@sep + @arr, n, 1) = @sep
)
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
and if your SQL version is lower than 2008 then you can use the any of the above solution..
Check the following simple example taken...
Declare @doc xml --xml parameter which passed thru
Declare @idoc int -- out parameter use in open xml
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
--here insert the selected records to table
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),ContactName varchar(20))
Game Over
Member
21 Points
28 Posts
Insert Multiple Record IN Sql using StoredProcedure
Feb 15, 2010 12:23 PM|LINK
I am going to insert Multiple record IN sql using storedProcedure from gridview...
for this purpose i am using cell concatinating method and send whole gridview cell data in string variable.
i have tried xml document method or sending data in table through looping as well
.....but didn't satisfy from both of them... b/c concatinating method required a delimeter for seperation(for which i have restrict the user from that sepereator ) and xml method required an extra file which is not a efficient approach ..
want to know any different way to do this...
any idea would be appreciated.....
ramireddyind...
All-Star
31358 Points
4579 Posts
Re: Insert Multiple Record IN Sql using StoredProcedure
Feb 15, 2010 12:46 PM|LINK
Send all the Data using delimeter seperated format..
suppose, u wants to use , as delimeter, concatenate the string using that.. and send that string to back end..
in back-end, divide that string and insert it.
Below function will divide a comma seperated string into multiple values..
syntax: select * from fn_split(@yourstring, 'your delimiter')
function:
USE [master] GO /****** Object: UserDefinedFunction [dbo].[fn_split] Script Date: 02/15/2010 19:16:00 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fn_split](@arr AS NVARCHAR(2000), @sep AS NCHAR(1)) RETURNS TABLE RETURN( WITH Numbers AS ( SELECT TOP 2000 ROW_NUMBER() OVER(ORDER BY sc1.NAME) AS N FROM Master.dbo.SysColumns sc1 ) SELECT SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n) AS element,row_number() over (order by (select null)) as pos FROM Numbers WHERE n <= LEN(@arr) + 1 AND SUBSTRING(@sep + @arr, n, 1) = @sep )Game Over
Member
21 Points
28 Posts
Re: Insert Multiple Record IN Sql using StoredProcedure
Feb 15, 2010 01:23 PM|LINK
ramireddyindia plz read my post again ... i dont want to use delemeter technique....
it restrict user to enter that delimeter ....(which i use to seperater my columnss cell)
gopalanmani
Star
7826 Points
1320 Posts
Re: Insert Multiple Record IN Sql using StoredProcedure
Feb 15, 2010 01:30 PM|LINK
Hi,
check the following URLs,
how to save multiple rows in gridview:
http://www.dotnetbips.com/articles/4fbf57bb-e777-453e-8b78-2009a922c5ec.aspx
XML To Send Multiple Rows At Once Into A Stored Procedure:
http://weblogs.sqlteam.com/travisl/archive/2005/01/04/3931.aspx
Inserting multiple records using one stored procedure:
http://www.vikramlakhotia.com/Inserting_multiple_records_using_one_stored_procedure.aspx
Gopalan Mani
My Tech blog
Game Over
Member
21 Points
28 Posts
Re: Insert Multiple Record IN Sql using StoredProcedure
Feb 16, 2010 05:28 PM|LINK
hi GOPALANMANI..
not a efective solution..
KeyurN
Member
105 Points
201 Posts
Re: Insert Multiple Record IN Sql using StoredProcedure
Apr 26, 2010 08:51 AM|LINK
Hi
Using openxml in Stored procedure,you can insert multiple record in one statment.for that you have to create the XML for your record.
Keyur N
yrb.yogi
Star
14460 Points
2402 Posts
Re: Insert Multiple Record IN Sql using StoredProcedure
Apr 26, 2010 09:04 AM|LINK
If you are using SQL Server 2008, than you can pass the table as parameter to insert the multiple records at one try..
Check this link to know
http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/60240.aspx
and if your SQL version is lower than 2008 then you can use the any of the above solution..
Check the following simple example taken...
Declare @doc xml --xml parameter which passed thru Declare @idoc int -- out parameter use in open xml SET @doc =' <ROOT> <Customer CustomerID="VINET" ContactName="Paul Henriot"> <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00"> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/> </Order> </Customer> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"> <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00"> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/> </Order> </Customer> </ROOT>' --Create an internal representation of the XML document. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- Execute a SELECT statement that uses the OPENXML rowset provider. --here insert the selected records to table SELECT * FROM OPENXML (@idoc, '/ROOT/Customer',1) WITH (CustomerID varchar(10),ContactName varchar(20)).Net All About
Joshi Amit
Member
616 Points
93 Posts
Re: Insert Multiple Record IN Sql using StoredProcedure
Apr 26, 2010 09:06 AM|LINK
Use Cell concatenation method to generate a XML file like
<Row1>value1</Row1>
<Row2>Value</Row2>
.......
......
<RowN>Value</RowN>
Pass this xml as string to your store Pocedure. Use OpenXML to convert this xml to table and fire insert statement from this table.
http://amitjoshi1980.blogspot.com/
- Helping others helps me to learn more.