Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Sending multiple rows to the Database from an Application: Part I

By Mohd Nizamuddin, (first published: 2009/08/18)

Introduction

We have all faced the problem while designing our applications using MS SQL Server 2005 where we want to send multiple rows needing to be modified (inserted or updated or deleted) to the database. We already know how to return a set of rows from the database to an application, but we did not have any features provided until SQL Server 2005 that would allow us to send multiple rows to the database.

I have tried to present the following two efficient approaches to work around this problem. They are:

  1. Delimited String Approach - explained in this article
  2. XML data approach - explained in part II of this article.

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 that 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.

This approach will be covered in Part II of this article.

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].[TableFormDelimitedString]
(
@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 [TableFormDelimitedString]('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].[TableFormDelimitedStringWithoutNumberList] 
(
@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 [TableFormDelimitedStringWithoutNumberList]('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. TableFormDelimitedString and TableFormDelimitedStringWithoutNumberList 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 TableFormDelimitedString (@sName, @delimeter) NameList
INNER JOIN TableFormDelimitedString (@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 in to 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

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. Hope, this can help you get around the multiple row send problem while designing your applications using SQL server 2005.


Watch this space for part II of this article, Sending multiple rows to the Database from an Application: Part II, where I will to explain the XML Data Approach using XML data and XQuery functions of SQL Server 2005.


Happy coding

Total article views: 27260 | Views in the last 30 days: 2
 
Related Articles
BLOG

Tally Table - Delimited list to Table

Dealing with delimited lists (Usually separated by a comma) in SQL is a problem easily handled by a ...

ARTICLE

Generating a Distinct Delimited List Using XML

Generating an ordered, distinct, delimited string using ROW_NUMBER() and FOR XML PATH.

FORUM

"function"

"function"

SCRIPT

Delimited String Parsing Functions - Big Set

Feed it large strings of delimited horizontal data and it returns it back as a vertical table.

SCRIPT

Delimited String Parsing Functions - Big2D set

Feed it large strings of double-delimited horizontal data and it returns it back as a non-pivoted ve...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones