O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

9.5. Performing a Bulk Insert with SQL Server

Problem

Given many records in an XML file that you need to add to a SQL Server 2000 database, you need to perform a bulk insert with optimal performance.

Solution

Perform a fast bulk insert and update using the XML bulk load functionality in Microsoft SQL Server 2000.

You’ll need a reference to the Microsoft SQLXML BulkLoad 3.0 Type Library from the COM tab in Visual Studio .NET’s Add Reference Dialog.

The sample uses a single XSD file:

Customers.xsd

The schema for the data that is bulk loaded into the Customers table

The sample uses a single XML file:

Customers.xml

Contains the data that is bulk loaded into the Customers table

The sample code creates a bulk load object SQLXMLBulkLoad and sets the connection string and error log file for the object. The Execute( ) method of the SQLXMLBulkLoad object is used to bulk load the Customers data in the XML file into the Customers table in the Northwind database. The Customers table must be empty prior to running this sample, otherwise, a primary key constraint error will be raised and written to the error log.

The Customers XSD file is shown in Example 9-7, and the XML file is shown in Example 9-8.

Example 9-7. File: Customers.xsd

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema"> <xsd:element name="ROOT" sql:is-constant="true"> <xsd:complexType> <xsd:sequence> <xsd:element ref="Customers" /> </xsd:sequence> </xsd:complexType> </xsd:element> ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required