Sending Multiple Rows to Database for Modification

• Download source code - 1.32 KB
• Download demo - 10.58 KB

Introduction

We have all faced this problem while designing our applications using Microsoft SQL Server 2005, when we want to send multiple rows to be modified (to be inserted or updated or deleted) to the database. We already know how to return a set of rows from the database to application, but we do not have any feature provided till the SQL Server 2005, to send multiple rows to the database.

In this article, I have tried to present the following two efficient approaches to work around this problem:

1. Delimited string approach
2. XML data approach

Delimited String Approach

In this approach, we send a delimited string of data corresponding to each column of a table, to a Stored Procedure (SP) from the application layer. The SP should then have functionality to parse the delimited string to extract the data values and then modify the record.

Whenever parsing comes into the picture, we only think about fetching values one by one and modifying the record in a loop, which in turn creates multiple calls to the database engine. As we all know, if we request the database at once for this kind of processing, there will be an appreciable gain in the performance of the SP.

I have illustrated two implementations of the delimited string approach in the examples in subsequent sections.

XML Data Approach

In this approach, we create an XML string at our application layer and send it to the SP. The SP should then have the functionality to parse the XML to extract the data elements and then modify the record. The advantage here is that we don't need to code for parsing the XML explicitly, as SQL Server 2005 can do this for us.

Delimited String Approach Implementation

For the delimited string approach, I have presented the following two implementations based on my experience:

1. Table valued function using Numbered List: This table valued function will create a numbered list and then using SUBSTRING and CHARINDEX inbuilt functions would convert the delimited string into a table.
2. Table valued function using recursive CTE (Common Table Expression): This table valued function also uses SUBSTRING and CHARINDEX inbuilt functions, along with a recursive CTE.

Table valued function using Number List

First I will explain the pieces of code, which form the building blocks of the final table valued function. We need to create a number list using the CTE as below:

;WITH
    L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
    L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
    L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
    L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
    L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
    Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
SELECT * FROM Numbers

This CTE is creating the list of numbers from 1 to POWER(POWER(POWER(POWER(2, 2), 2), 2), 2), i.e. until 65536.

Now consider the below code snippet, where @list and @delim variables have been assigned.

DECLARE
	@list NVARCHAR(MAX), @delim NCHAR(1)

SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ','

;WITH
	L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
	L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
	L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
	L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
	L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
	Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4
)
SELECT
	@list List,
	SUBSTRING(@list, Number, CHARINDEX(@delim, @list + @delim, Number) - _
							Number) AS Value,
	Number AS StartingFrom,
	CHARINDEX(@delim, @list + @delim, Number) AS DelimeterPosition
FROM Numbers
WHERE Number <= CONVERT(INT, LEN(@list))
  AND SUBSTRING(@delim + @list, Number, 1) = @delim

The SUBSTRING statement cuts characters from @list starting from character position (1, 5, 11 and 16).

SUBSTRING(@list, Number, CHARINDEX(@delim, @list + @delim, Number) – Number)

The number of characters to be cut is decided by CHARINDEX which will return 4, 10, 15, 20 in each row, where it finds the delimiter character.

CHARINDEX(@delim, @list + @delim, Number) - Number

The above SELECT only works until the number of characters present in the @list variable due to the condition:

Number <= CONVERT(INT, LEN(@list))

The duplicate values are filtered out from the output list by the “WHERE” condition created using the SUBSTRING function which will only return a value when it finds the delimiter.

SUBSTRING(@delim + @list, Number, 1) = @delim

The output of the code snippet above would be:

List                    Value   Starting From   Delimiter Position 
------------------------------------------------------------------
aaa,bbbbb,cccc,dddd     aaa         1                   4
aaa,bbbbb,cccc,dddd     bbbbb       5                   10
aaa,bbbbb,cccc,dddd     cccc        11                  15
aaa,bbbbb,cccc,dddd     dddd        16                  20

Table valued function using Numbered List: Implementation

Now combining all the above explained pieces of SQL, we create our table valued function which will parse the string and return a table having two columns viz. ID and Data.

CREATE FUNCTION [dbo].[TableFormDelimetedString]
(
	@param		NVARCHAR(MAX),
	@delimeter	NCHAR(1)
)
RETURNS @tmp TABLE
(
	ID		INT 	IDENTITY (1, 1),
	Data		Varchar(MAX)
)
BEGIN
;WITH
    L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
    L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
    L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
    L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
    L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
    Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
INSERT INTO @tmp (Data)
SELECT LTRIM(RTRIM(CONVERT(NVARCHAR(4000),
		SUBSTRING(@param, Number,
			CHARINDEX(@delimeter, @param + @delimeter, Number) - Number
				)
		))) AS Value
       FROM   Numbers
       WHERE  Number <= CONVERT(INT, LEN(@param))
         AND  SUBSTRING(@delimeter + @param, Number, 1) = @delimeter
RETURN
END

Table valued function using Numbered List: Usage

So if we now invoke the above function like:

SELECT * FROM [TableFormDelimetedString]('Andy:Roger:Thomas:Rob:Victor',':') 

We will obtain the following result set:

ID      Data
--------------------
1       Andy
2       Roger
3       Thomas
4       Rob
5       Victor

Table valued function using recursive CTE

Here again, I will first explain the pieces of code which form the building blocks of the final table valued function. As we know, in a recursive CTE, we have one anchor part and one recursive part. But if we create a CTE having only the anchor part, it would look something like:

DECLARE
	@list NVARCHAR(MAX), @delim NCHAR(1)

SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ','

;WITH CTETable (start, stop)
AS
(
	SELECT start = CONVERT(bigint, 1), stop = CHARINDEX(@delim, @list + @delim, 1)
)
SELECT @list List, LTRIM(RTRIM(SUBSTRING(@list, start,
				 CASE
				 WHEN stop > 0
				 THEN stop - start
				 ELSE 0
				 END
				))) AS Data
	start AS StartingFrom, stop AS DelimiterPosition
FROM CTETable    

The output of the SQL above will be like:

List                    Value   Starting From   Delimiter Position 
------------------------------------------------------------------
aaa,bbbbb,cccc,dddd     aaa         1               4

Now by adding a recursive member to the above CTE, which iterates over the stop variable, the SQL looks like:

DECLARE
	@list NVARCHAR(MAX), @delim NCHAR(1)

SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ','

;WITH CTETable (start, stop)
AS
(
	SELECT start = CONVERT(bigint, 1), stop = CHARINDEX(@delim, @list + @delim, 1)
	UNION ALL		-- added for recursive part of CTE
	SELECT start = stop + 1, stop = CHARINDEX(@delim, @list + @delim, stop + 1) _
	     FROM CTETable WHERE stop > 0	-- added for recursive part of CTE
)
SELECT @list List, LTRIM(RTRIM(SUBSTRING(@list, start,
				 CASE
				 WHEN stop > 0
				 THEN stop - start
				 ELSE 0
				 END
				))) AS Data
	start AS StartingFrom, stop AS DelimiterPosition
FROM CTETable
WHERE stop > 0    

And gives the following result set:

List                    Value   Starting From   Delimiter Position 
------------------------------------------------------------------
aaa,bbbbb,cccc,dddd     aaa         1               4
aaa,bbbbb,cccc,dddd     bbbbb       5               10
aaa,bbbbb,cccc,dddd     cccc        11              15
aaa,bbbbb,cccc,dddd     dddd        16              20    

Table valued function using recursive CTE: Implementation

Finally we create a table valued function from the above code blocks, which looks like:

CREATE FUNCTION [dbo].[TableFormDelimetedStringWithoutNumberList]
(
	@list	NVARCHAR(MAX),
	@delim	NCHAR(1) = ','
)
RETURNS @tmp TABLE
(
	ID	INT     IDENTITY (1, 1),
	Data	Varchar(MAX)
)
BEGIN
	;WITH CTETable (start, stop)
	AS
	(
		SELECT start = CONVERT(bigint, 1),
			stop = CHARINDEX(@delim, @list + @delim)
		UNION ALL	-- added for recursive part of CTE
		SELECT start = stop + 1,
		  stop = CHARINDEX(@delim, @list + @delim, stop + 1)-- added for 
							-- recursive part of CTE
		FROM   CTETable
		WHERE  stop > 0
	)
	INSERT INTO @tmp (Data)
	SELECT LTRIM(RTRIM(SUBSTRING(@list, start,
		CASE
			WHEN stop > 0
			THEN stop - start
			ELSE 0
		END))) AS Data
	FROM CTETable
	WHERE stop > 0
RETURN
END

Table valued function using recursive CTE: Usage

So if we now invoke the above function like:

SELECT * FROM [TableFormDelimetedStringWithoutNumberList]_
			('Andy:Roger:Thomas:Rob:Victor', ':')

We will obtain the following result set:

ID  Data 
-------------
1   Andy
2   Roger
3   Thomas
4   Rob
5   Victor 

Why I like these two implementations is because the looping has been handled by the SQL server database engine itself, which would definitely be more efficient than explicit SQL looping code written by a developer.

Using the table valued function Implementations

As we know that the two table valued function implementations viz. TableFormDelimetedString and TableFormDelimetedStringWithoutNumberList explained above, return a table from a delimited string along with its position as ID, we will now see how to invoke them from a sample SP.

For the purpose of this illustration, I have used the table below, wherein we need to insert multiple records sent by the application layer.

--Creating a base table
CREATE TABLE Emp(
	ID		    INT		IDENTITY (1, 1),
	Name		VARCHAR(50),
	Salary	    INT
);

To invoke these two functions, we create an SP which takes multiple delimited strings and a delimiter as input. This SP will call one of the two table valued functions, which would in turn convert the delimited string into a table.

In our sample SP, we have employee names and their salaries in two different delimited strings, which need to be parsed. As the table valued functions described above can transform a delimited string into a table with an ID column, we will use this ID column to join two different tables, created from the two different delimited strings, i.e. employee names and salaries.

Using the table valued function Implementations: Sample SP Implementation

The sample SP, which takes multiple parameters and inserts the records into the base table (Emp), looks like:

CREATE PROCEDURE InsertEmp1
(
	@sName	NVARCHAR(MAX),
	@sSalary	NVARCHAR(MAX),
	@delimeter	CHAR (1)
)
AS
BEGIN
	INSERT INTO Emp (Name, Salary)
	SELECT NameList.Data, SalaryList.Data
	FROM TableFormDelimetedString (@sName, @delimeter) NameList
		INNER JOIN TableFormDelimetedString (@sSalary, @delimeter) SalaryList
			ON NameList.ID = SalaryList.ID
RETURN 0
END

Using the table valued function Implementations: Sample SP Usage

Here I am passing two strings “'Andy:Roger:Thomas:Rob:Victor'”,” '100:200:1000:500:50'” delimited by “:” to the SP, which will insert the corresponding records into the target table.

EXEC InsertEmp1 'Andy:Roger:Thomas:Rob:Victor', '100:200:1000:500:50', ':'

We can see the result by selecting from the target table:

SELECT * FROM Emp

ID  Name        Salary
------------------------
1   Andy        100
2   Roger       200
3   Thomas      1000
4   Rob         500
5   Victor      50 

Sending XML Data to SP

When we create an XML data, we have two different methods to put data into the XML:

• Attribute based
• Element based

Parsing Attribute Based XML

In this, we put our target data in the attribute, which is enclosed by specific elements. Basically we need to create hierarchical data in XML format.

Let’s consider that we want to send name and salary of multiple employees, we create an XML like below:

Declare @xml	XML
SET @xml = N'
<ROWS>
	<ROW Name="Richard" Salary="1100"/>
	<ROW Name="Cliff" Salary="1200"/>
	<ROW Name="Donna" Salary="13000"/>
	<ROW Name="Ann" Salary="1500"/>
</ROWS>'
SELECT T.Item.value('@Name', 'VARCHAR(50)') Name,
	T.Item.value('@Salary', 'INT') Salary
	FROM   @xml.nodes('/ROWS/ROW') AS T(Item)

First, I would like to explain different XQuery functions, which I have used to get data from the above XML and used it in the SP. In above XML variable, my top level element is ROWS, which has multiple ROW tags, which further contains actual data tags, e.g. Name and Salary as attributes.

Look at the FROM clause of the above mentioned SQL, it has nodes() function taking target element path starting from root element, from where data is to be read. The nodes() function returns a row set that contains logical copies of XML data. Using value() function, you can retrieve multiple values from the row set. The value() function takes two parameters, first would be attribute name prefixed by “@” and the second would be SQL Server data type to convert that value. We need the data type conversion because all attribute values are considered as string.

For the purpose of this illustration, I have used the table below wherein we need to insert multiple records sent by the application layer.

SP Implementation: parsing attribute based XML

Below SP reads data from attribute based XML and inserts into our base table (Emp):

CREATE PROCEDURE [dbo].[InsertEmp2]
(
	@xml		XML
)
AS
BEGIN
	INSERT INTO Emp (Name, Salary)
	SELECT T.Item.value('@Name', 'VARCHAR(50)') Name,
		T.Item.value('@Salary', 'INT') Salary
	FROM   @xml.nodes('/ROWS/ROW') AS T(Item)
RETURN 0
END

Note: XQuery functions are case sensitive.

SP Implementation: parsing attribute based XML: Usage

Here I am creating an XML variable and populating the xml variable with valid XML having elements and attributes, which I am passing to the SP. If you do not have any attribute value, then you can omit the whole attribute, which will return NULL. Make sure that your target table accepts NULL in that column.

Declare @x	XML
SET @x = N'
<ROWS>
    <ROW Name="Richard" Salary="1100"/>
    <ROW Name="Cliff" Salary="1200"/>
    <ROW Name="Donna" Salary="13000"/>
    <ROW Name="Ann" Salary="1500"/>
</ROWS>'

EXEC InsertEmp2 @x
SELECT * FROM Emp

ID  Name        Salary 
--------------------------
1   Richard     1100
2   Cliff       1200
3   Donna       13000
4   Ann         1500

Parsing Element Based XML

In this, we put our target data under elements. Basically we need to create a hierarchical data in XML format. If we wanted to send name and salary of multiple employees, we create XML like below:

Declare @xml	XML
SET @xml = N'
<ROWS>
    <ROW>
        <Name>Tom</Name>
        <Salary>2100</Salary>
    </ROW>
    <ROW>
        <Name>Nuk</Name>
        <Salary>2200</Salary>
    </ROW>
    <ROW>
        <Name>Gilbert</Name>
        <Salary>2300</Salary>
    </ROW>
    <ROW>
        <Name>Mat</Name>
        <Salary>2600</Salary>
    </ROW>
</ROWS>'

Here each ROW element is designated to a record of an employee, containing name and salary. These rows are enclosed by top level element called ROWS. Each ROW element contains two elements, name and salary respectively. Further these elements (name and salary) have data between start and end element XML tags.

SELECT  T.Item.query('./Name').value('.', 'VARCHAR(50)') Name,
		T.Item.query('./Salary').value('.', 'INT') Salary
	FROM   @xml.nodes('/ROWS/ROW') AS T(Item)

Earlier we talked about the nodes() and value() XQuery functions. Now, I would like to explain about the query() function, which is used here. The query() function takes one XQuery expression as a parameter (in our case './Name' or './Salary') and returns specified XML element in XML format like:

query('./Name')

would return:

<Name>……</Name> 

from the rowset.

But if you look at value() function.

value('.', 'VARCHAR(50)')

It has a dot “.” as the first parameter, which specifies that read the value of current element and second parameter would convert the read value into specified SQL Server data type.

SP Implementation: parsing element based XML

Below SP reads data from an element based XML and inserts into our base table (Emp):

CREATE PROCEDURE InsertEmp3
(
	@xml		XML
)
AS
BEGIN
	INSERT INTO Emp (Name, Salary)
	SELECT  T.Item.query('./Name').value('.', 'VARCHAR(50)') Name,
		T.Item.query('./Salary').value('.', 'INT') Salary
	FROM   @xml.nodes('/ROWS/ROW') AS T(Item)
RETURN 0
END

SP Implementation: parsing element based XML: Usage

Here I am creating an XML variable and populating it with valid XML having elements, which I am referring into the SP. If you do not have any element value, then you can omit the whole element, e.g.

<ROWS>
    <ROW>
        <Name>Tom</Name>
        <Salary>2100</Salary>
    </ROW>
    <ROW>
        <Name>Nuk</Name>
    </ROW>
    <ROW>
        <Name>Mat</Name>
        <Salary>2600</Salary>
    </ROW>
</ROWS>

In the above XML, I do not have any data pertaining to second employee “Nuk”. So I did not include the salary element for it, at all.

Declare @x	XML
SET @x = N
    '<ROWS>
        <ROW>
            <Name>Tom</Name>
            <Salary>2100</Salary>
        </ROW>
        <ROW>
            <Name>Nuk</Name>
            <Salary>2200</Salary>
        </ROW>
        <ROW>
            <Name>Gilbert</Name>
            <Salary>2300</Salary>
        </ROW>
        <ROW>
            <Name>Mat</Name>
            <Salary>2600</Salary>
        </ROW>
    </ROWS>'

EXEC InsertEmp3 @x
SELECT * FROM Emp    

ID  Name        Salary 
--------------------------
1   Richard     1100
2   Cliff       1200
3   Donna       13000
4   Ann         1500
5   Tom         2100
6   Nuk         2200
7   Gilbert     2300
8   Mat         2600 

Comparison Between Parsing of Delimited String and XML Data

In this article, I have tried to present the following two efficient approaches to work around the problem of sending multiple rows to the database for update:

1. Delimited string approach
2. XML data approach

I now present a comparison of both these approaches highlighting the pros and cons of each approach.

Delimited String Approach

Pros

• Easy to parse at the database side
• Easy to create at the application layer

Cons

• Manual parsing, need to write logic to parse (In our case, we have written table valued functions.)
• Need to pass as many delimited strings as the number of parameters
• Since delimited string would be passed as a string data type, so limitation of size of string data supported in SQL Server 2005 comes into picture (4000 / 8000 characters max including the delimiter character)
• Choosing a delimiter character, as it should never occur in your data

XML Data

Pros

• Supports large size of data (up to 2 GB)
• Only one parameter needs to be sent from application layer
• No manual parsing, handled by XQuery functions
• Passed XML can be validated with an XSD

Cons

• Complex to parse (need to have knowledge of XQuery functions)
• Difficult to create XML data at the application layer

Summary

In this article, we discussed how to parse a delimited string in SQL Server 2005, using the two table valued function implementations, without writing an explicit loop statement. We also discussed CTEs (Common Table Expression), one of the nicest (albeit a little complex) feature of the SQL Server 2005 and the XQuery functions and how to convert an XML into a table using them.

Hope, this can help you get around the multiple row send problem while designing your applications using SQL server 2005.

This article would also be helpful where an application works in disconnected environment. A user can save data in a disconnected environment using either of these two approaches. When the user will be online or connected to database and wants to update the modified records, all that data can be passed to the database for updating using either a delimited string or an XML.

Using the Code

This article has two parts:

• T-SQL
• Client application

T-SQL

You need to download the attached ZIP file and run the .SQL file at your database instance. Once you create a table, table-valued functions and procedures, you can execute the SP as shown above.

Client Application

The attached ZIP file contains a client application developed using C# for demo purposes.

Points of Interest

Here we discussed about:

• Table-valued function
• Inbuilt T-SQL functions like CHARINDEX, SUBSTRING, etc.
• XML parsing
• XQuery functions
• How can we implement looping logic in single SELECT statement

History

• 18^th August, 2009: Initial version