Mass sending data to stored procedure

Question

Hi Gurus

I'm using Microsoft .NET Framework 3.5 to create a web service with VB.NET. I'm using a Stored Procedure in SQL Server 2008 so that SQL can insert all the data that I'm passing.

The problem is that in one of the servicse I need to pass around 10,000 records and it's not very efficient to run the stored procedure 10,000 times.

I read that there is a way in which you can pass an XML file with all data to the Stored Procedure but I'm not sure if that's the most efficient way. Also I couldn't make the code work, I don't know if I have to pass the XML as a String.

I'm asking for help with a method in which I can pass a lots of records to the stored procedure once and then the same instance of the Stored procedure can process all the records in a loop

Thank you all in advance.

Answer 1

http://msdn.microsoft.com/en-us/library/ms186918.aspx

This may also help: http://sqlxml.org/faqs.aspx?faq=61

Answer 2

The method I use is to pass the data in a CDATA block (pipe delimited in my case) to the web service, which then:

1. Saves the CDATA block to a temp file on the web server
2. Then uses the command line utility bcp.exe to bulk load the data into a staging table
3. Then calls the stored procedure which is set up to process all the records in the staging table

much faster and less strain on the database than calling the proc for each record.

Edit: Now that I've read about SqlBulkCopy, I would do this instead:

1. Write the CDATA block data into a DataTable
2. Use SqlBulkCopy to put the data into a staging table :-)
3. Then calls the stored procedure which is set up to process all the records in the staging table

Answer 3

There is SqlBulkCopy in .NET, but I expect you'll want to look at a table-valued parameter.

Answer 4

Where I've done this before with SQL Server 2000, I've used OPENXML (as @EJB suggested) by constructing an XML string in my code, passing it into a stored proc in a text parameter, using OPENXML to parse the XML into a relational structure and going on from there e.g.

VB

Dim xmlStringBuilder As System.Text.StringBuilder

xmlStringBuilder = New System.Text.StringBuilder
xmlStringBuilder.Append("<objects>"
For Each object In Collection
    'I'm not suggesting this is the best way to build XML, it is however reliable!
    xmlStringBuilder.Append("<object id='" & object.id.ToString & "'></object>"
Next
xmlStringBuilder.Append("</objects>"

Dim xmlStoredProcCommand As SqlCommand
Dim xmlParameter As SqlParameter
xmlStoredProcCommand = New SqlCommand(connection)
xmlStoredProcCommand.CommandType = CommandType.StoredProcedure
xmlStoredProcCommand.CommandText = "xmlStoredProc"
xmlParameter = New SqlParameter("@xmlParameter",SqlDbType.NText)
xmlParameter.Value = xmlStringBuilder.ToString
xmlStoredProcCommand.Parameters.Add(xmlParameter)
xmlStoredProcCommand.ExecuteNonQuery

SQL

CREATE PROCEDURE xmlStoredProc
@xmlParameter NTEXT 
AS
BEGIN
    DECLARE @xmldochandle INT
    DECLARE @objects TABLE (objectID INT)

    EXEC sp_xml_preparedocument @xmldochandle OUTPUT, @xmlParameter

    INSERT INTO @objects (objectId)
    SELECT objectId
    FROM OPENXML(@xmldochandle, 'objects/object')
    WITH (objectId INT)

    EXEC sp_xml_removedocument @xmldochandle
END

and from there you can do your stuff with the contents of the @objects table variable.