Carter's Dad - Parker's Dad Too!

From time to time, people ask the question "How can I insert many records at once into SQL Server?"  The purpose of this quick article is to describe that process in very basic details.  I'll give a very simple example, so that you can then take it from there.

I find that it's easiest to visualize if you start with a very simple XML document.  Here is an example XML snippet:

<items>

            <item id="1"></item>

            <item id="4"></item>

            <item id="12"></item>

            <item id="112"></item>

            <item id="234"></item>

            <item id="605"></item>

</items>

Let's say you have a checkboxlist, and you want to send the list of "checked" items into a SQL Server stored procedure, making an INSERT for each checked item.  You want to create a string containing text that is formatted as above, and for each checked item, insert a "row."  Here is a simple class in VB.NET to abstract that functionality:

Public Class XMLItemList

    Private sb As System.Text.StringBuilder

    Public Sub New()

        sb = New System.Text.StringBuilder

        sb.Append("<items>" & vbCrLf)

    End Sub

    Public Sub AddItem(ByVal Item As String)

        sb.AppendFormat("<item id={0}{1}{2}></item>{3}", Chr(34), Item, Chr(34), vbCrLf)

    End Sub

    Public Overrides Function ToString() As String

        sb.Append("</items>" & vbCrLf)

        Return sb.ToString

    End Function

End Class

As you can see, there is nothing special or fancy going on here.  The purpose of the class is just to make the next example easier to understand:

Dim MyXMLItemList As New XMLItemList

For i As Integer = 0 To MyCheckBoxList.Items.Count - 1

    If MyCheckBoxList.Items(i).Selected = True Then

        MyXMLItemList.AddItem(MyCheckBoxList.Items(i).Value)

    End If

Next

Given the above, you can use the MyXMLItemList.ToString method to get a string representation that is formatted like the XML snippet described in the beginning.

All that remains then is to call your stored procedure, passing in the string produced above as a parameter.  Here is how that stored procedure would be written:

alter procedure usp_InsertManyRows

@XMLDOC varchar(8000)

AS

declare @xml_hndl int

--prepare the XML Document by executing a system stored procedure

exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC

--insert into table

Insert Into testtable

            (

            InsertedID

            )

Select

            IDToInsert

From

            OPENXML(@xml_hndl, '/items/item', 1)

            With

                        (

                        IDToInsert int '@id'

                        )

Now follows a comparison of how each performs.  The following tests were run with simple ASP.NET pages using a local SQL Server database.  The results are quite dramatic, and would only be more dramatic if the hits had to go over the network:

First, I created a simple page that hit the database 10 times, each time inserting one record.  Here are the results of that test:

            97 Requests Per Second

            28254 hits in 5 minutes

            282540 records inserted

Next, I created a simple page that hits the database only once, sending in all ten records to be inserted together at once, using the XML format mentioned in this article.  Here are the results of that test:

            186 Requests Per Second

            55349 hits in 5 minutes

            553490 records inserted

Quite an impressive difference!