Insert many rows into MSSQL2005 using XML
This troubled me for a long time... Here's my solution in VB.net
Dim xmlSource As New StringBuilder
dim recordsToCreate
recordsToCreate = 100000
for x = 1 to recordsToCreate
xmlSource.Append("<row>")
xmlSource.Append("<Col1>SomeData in col1 " & x & "</Col1>")
xmlSource.Append("<Col2>SomeData in col2 " & x & "</Col2>")
xmlSource.Append("</row>")
next
Dim startTime As Date = Now
' Run the process that is to be timed.
Using con As New SqlConnection( connStr )
con.Open()
strSQL = "DECLARE @xml XML; SET @xml=@xmlSource; "
strSQL = strSQL & "INSERT INTO XDELETEME (col1, col2) Select "
strSQL = strSQL & "Tbl.Col.value('Col1[1]', 'nvarchar(50)'), Tbl.Col.value('Col2[1]', 'nvarchar(50)') FROM @xml.nodes('//row') Tbl(Col) "
cmd = New SqlCommand(strSQL, con)
With cmd.Parameters
.Add(New SqlParameter("@xmlSource", xmlSource.toString() ))
End with
cmd.ExecuteNonQuery()
con.Close()
End using
Dim runLength As Global.System.TimeSpan = Now.Subtract(startTime)
Dim millisecs As Integer = runLength.Milliseconds
response.write(recordsToCreate & " records imported in " & millisecs & " Milliseconds")
This works really well for bulk inserts.
.PNG)
