10.10. Bulk Copying with SQL Server
Problem
You need to bulk copy data into a SQL server table from another SQL Server table, or from an external file such as an XML file.
Solution
Use the SqlBulkCopy
class.
The solution shows how to bulk copy data into a SQL Server table from two sources: a DataReader
and an XML file.
Both solutions copy data into a table named AddressExpand
in the database AdoDotNet35Cookbook
. Execute the following T-SQL statement to create the table:
USE AdoDotNet35Cookbook GO CREATE TABLE AddressExpand( AddressID int NOT NULL PRIMARY KEY, AddressLine1 nvarchar(60) NOT NULL, AddressLine2 nvarchar(60) NULL, City nvarchar(30) NOT NULL, StateProvinceName nvarchar(50) NOT NULL, PostalCode nvarchar(15) NOT NULL, CountryRegionName nvarchar(50) NOT NULL )
The first solution creates a DataReader
on the join of three tables in AdventureWorks
that returns denormalized addresses for the data in the Person.Address
table in AdventureWorks
.A IDataReader
instance is created for the query and used with the WriteToServer()
method of the SqlBulkCopy
class to perform the bulk copy into the AddressExpand
table in the AdoDotNet35Cookbook
database.
The C# code in Program.cs in the project BulkCopyDataReader
is shown in Example 10-17.
Example 10-17. File: Program.cs for BulkCopyDataReader solution
using System; using System.Data; using System.Data.SqlClient; namespace BulkCopyDataReader { class Program { static void Main(string[] args) { string srcConnectString = "Data Source=(local);" + "Integrated ...
Get ADO.NET 3.5 Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.