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!