Creating Tables for the Bulk Copy ExamplesÂ
The SqlBulkCopy class can be used to write data only to SQL Server tables. The code samples shown in this topic use the SQL Server 2005 sample database, AdventureWorks. To avoid altering the existing tables code samples write data to tables that you must create first.
The BulkCopyDemoMatchingColumns and BulkCopyDemoDifferentColumns tables are both based on the AdventureWorks Production.Products table. In code samples that use these tables, data is added from the Production.Products table to one of these sample tables. The BulkCopyDemoDifferentColumns table is used when the sample illustrates how to map columns from the source data to the destination table; BulkCopyDemoMatchingColumns is used for most other samples.
A few of the code samples demonstrate how to use one SqlBulkCopy class to write to multiple tables. For these samples, the BulkCopyDemoOrderHeader and BulkCopyDemoOrderDetail tables are used as the destination tables. These tables are based on the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables in AdventureWorks.
Note
The SqlBulkCopy code samples are provided to demonstrate the syntax for using SqlBulkCopy only. If the source and destination tables are located in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT … SELECT statement to copy the data.
Table Setup
To create the tables necessary for the code samples to run correctly, you must run the following Transact-SQL statements in a SQL Server database.
USE AdventureWorks
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[BulkCopyDemoMatchingColumns]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[BulkCopyDemoMatchingColumns]
CREATE TABLE [dbo].[BulkCopyDemoMatchingColumns](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[ProductNumber] [nvarchar](25) NOT NULL,
CONSTRAINT [PK_ProductID] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[BulkCopyDemoDifferentColumns]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[BulkCopyDemoDifferentColumns]
CREATE TABLE [dbo].[BulkCopyDemoDifferentColumns](
[ProdID] [int] IDENTITY(1,1) NOT NULL,
[ProdNum] [nvarchar](25) NOT NULL,
[ProdName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ProdID] PRIMARY KEY CLUSTERED
(
[ProdID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[BulkCopyDemoOrderHeader]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[BulkCopyDemoOrderHeader]
CREATE TABLE [dbo].[BulkCopyDemoOrderHeader](
[SalesOrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[AccountNumber] [nvarchar](15) NULL,
CONSTRAINT [PK_SalesOrderID] PRIMARY KEY CLUSTERED
(
[SalesOrderID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[BulkCopyDemoOrderDetail]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[BulkCopyDemoOrderDetail]
CREATE TABLE [dbo].[BulkCopyDemoOrderDetail](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
CONSTRAINT [PK_LineNumber] PRIMARY KEY CLUSTERED
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC
) ON [PRIMARY]
) ON [PRIMARY]