Performing a Bulk Insert with SQL Server

[ Team LiB ] Recipe 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. | Team LiB Recipe 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 Type Library from the COM tab in Visual Studio .NET s Add Reference Dialog. The sample uses a single XSD file The schema for the data that is bulk loaded into the Customers table The sample uses a single XML file 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 xsd schema xmlns xsd http 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 xsd element name Customers sql relation Customers xsd complexType xsd sequence xsd element name CustomerID type xsd string sql datatype nvarchar 5 xsd element name CompanyName type xsd string sql datatype nvarchar 40 xsd element name ContactName type xsd string sql datatype nvarchar 30 xsd element name ContactTitle type xsd string sql datatype nvarchar 30 xsd element name Address type xsd string sql datatype nvarchar 60 xsd element name City type xsd string sql datatype nvarchar 15 xsd element name Region .