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:
- Delimited string approach
- 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:
- 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. - 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
SELECT start = stop + 1, stop = CHARINDEX(@delim, @list + @delim, stop + 1) _
FROM CTETable WHERE stop > 0
)
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
SELECT start = stop + 1,
stop = CHARINDEX(@delim, @list + @delim, stop + 1)
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.
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:
- Delimited string approach
- 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
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
- 18th August, 2009: Initial version
