Importing Data into SQL Server 2005

You may need to add information from legacy systems or databases into your database. There are several tools available for this purpose. In each situation, choosing the appropriate tool depends on the source, the amount of information, and occasionally on security considerations.

Using the BCP Utility

This command line utility allows you to import or export large amounts of data. As a command line utility, BCP can be used to initialize the database for an application during the application setup process. BCP can be used to fill some tables in your database with certain information according to user selections. As with any command line utility, you must understand the different switches and command options.

Note 

Refer to SQL Server Books Online for a complete list of options.

BCP requires the names of the source/destination database and table (or view), an action identifier such as in or out , and the name of the external data file. You may need to specify the server and instance name (if it is not the default instance), plus the username and password if you need to specify information other than the ID of the current user.

The basic BCP syntax is:

 BCP <Database>.<Schema>.<TableName>/<ViewName>          <in/out> <ExternalFileName> <SecurityModifier> <FormatModifier> 

The following example exports the records from the Product data table to a comma-separated values file.

 bcp AdventureWorks.Production.Product out "Products.txt" -T -c 

With that flat file, you can import the information into another database.

If you execute the following script (located in the sample files as  CREATE Production2 Table.sql ), you will create a new Product table named Product2 in the AdventureWorks database.

   CREATE TABLE [Production].[Product2]([ProductID] [int] IDENTITY(1,1) NOT NULL,    [Name] [dbo].[Name] NOT NULL,    [ProductNumber] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,    [MakeFlag] [dbo].[Flag] NOT NULL CONSTRAINT [DF_Product_MakeFlag2]  DEFAULT ((1)),    [FinishedGoodsFlag] [dbo].[Flag] NOT NULL CONSTRAINT [DF_Product_FinishedGoodsFlag2]  DEFAULT ((1)),    [Color] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [SafetyStockLevel] [smallint] NOT NULL,    [ReorderPoint] [smallint] NOT NULL,    [StandardCost] [money] NOT NULL,    [ListPrice] [money] NOT NULL,    [Size] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [SizeUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [WeightUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [Weight] [decimal](8, 2) NULL,    [DaysToManufacture] [int] NOT NULL,    [ProductLine] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [Class] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [Style] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,    [ProductSubcategoryID] [int] NULL,    [ProductModelID] [int] NULL,    [SellStartDate] [datetime] NOT NULL,    [SellEndDate] [datetime] NULL,    [DiscontinuedDate] [datetime] NULL,    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Product_rowguid2] DEFAULT (newid()),    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Product_ModifiedDate2]          DEFAULT (getdate()),  CONSTRAINT [PK_Product_ProductID2] PRIMARY KEY CLUSTERED ([ProductID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] GO   

You can then import the data by using the following syntax:

 bcp AdventureWorks.Production.Product2 in "Products.txt" -T -c 

When importing or exporting complex data table structures, it is advisable to precisely define what structure your flat file will have. You can do this by using the format modifier instead of in or out , using nul as the output file, and specifying a format file name as follows :

 bcp AdventureWorks.Production.ProductCategory format nul  -T -f categories.fmt -c 

The categories.fmt file in this example contains descriptors for each file. Since the c modifier instructs the BCP utility to export or import as a character, all of the columns are defined as characters .

1	SQLCHAR		12	"\t"	1	ProductCategoryID	""
2	SQLCHAR		100	"\t"	2	NameSQL_Latin1_General_CP1_CI_AS

However, if you want to use native SQL datatypes, you must use the N modifier instead of the c modifier.

Using the BULK INSERT Command

You need to import information into a database, but you find that you cannot use the BCP tool because security constraints in the users environment do not allow the user to open a command prompt window. There is a T-SQL sentence available to solve this problem. The BULK INSERT command requires arguments similar to the BCP command. At a minimum, it requires the name of the destination table and the file name for the source.

   BULK INSERT    [AdventureWorks].[Production].[Product2]    FROM 'C:\Products.txt'   

In addition, you can specify modifiers to set the row delimiter and column delimiter , to use a format file, and so on.