Tuesday, July 12, 2011

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.