In my previous tip, I discussed how to use the bcp utility in SQL Server to bulk-copy data from a file into a table or from a table into a file. In this tip, I discuss the BULK INSERT statement, which is a Transact-SQL statement that lets you bulk-load a data file into a SQL Server database. I also provide several examples of BULK INSERT statements to copy data into a SQL Server table. These examples have been tested in SQL Server 2005 and SQL Server 2008, and for all examples I used the AdventureWorks sample database.
To run the examples in this tip, I first used the following bcp command to create my source data file:
bcp "SELECT EmployeeID, FirstName, LastName, JobTitle FROM
AdventureWorks.HumanResources.vEmployee WHERE EmployeeID > 1" queryout
C:\Data\Employees.csv -c -T -t,
The command retrieves data from the vEmployee view in the AdventureWorks database and saves it to the C:\Data\Employees.csv file.
Next, I used the following T-SQL script to create the Employees table in the AdventureWorks database:
USE AdventureWorks
GO
IF OBJECT_ID (N'Employees', N'U') IS NOT NULL
DROP TABLE dbo.Employees
GO
CREATE TABLE dbo.Employees (
EmployeeID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
JobTitle NVARCHAR(50) NOT NULL,
CONSTRAINT ckJobTitle CHECK (JobTitle <> 'Marketing Technician' )
)
Note that, in the table definition, the EmployeeID column is configured as an IDENTITY column and the JobTitle column is configured with a check constraint. I've included both components to demonstrate the options supported by the BULK INSERT statement.
The syntax for the BULK INSERT statement is straightforward:
BULK I...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
NSERT TableView FROM 'DataFile'
[ WITH option [, ...]
As you can see, the BULK INSERT keywords are followed by the name of the table or view. That name should be qualified with the database and schema names as necessary. Following the name is the FROM clause, which specifies the path and file name of the data file. After the FROM clause, you should include any of the appropriate options supported by the BULK INSERT statement.
The following statement provides an example of the various elements included in the syntax:
BULK INSERT AdventureWorks.dbo.Employees
FROM 'C:\Data\Employees.csv'
WITH
(
FIELDTERMINATOR = ','
)
This statement retrieves data from the C:\Data\Employees.csv file and inserts that data into the Employees table in the AdventureWorks database. First, I defined the target table in the BULK INSERT clause. In this case, I used the table's fully qualified name. Next, I specified the file name in the FROM clause. Note that I enclosed the file name in single quotes, so it is treated as a string value.
The next step is to add the WITH clause, which includes the FIELDTERMINATOR option enclosed in parentheses. Because the source file is a comma-delimited file, I must specify that each field is terminated with a comma. I do so by including a comma (enclosed in single quotes) after the equal sign. Without specifying the FIELDTERMINATOR option for a .csv file, the BULK INSERT statement would not know how to retrieve the data and the statement would fail.
These are the general steps for using a SQL Server BULK INSERT statement. But the statement supports nearly 20 total statements, which is many more than what I've shown here. For instance, the following BULK INSERT statement includes two options -- FIELDTERMINATOR and CHECK_CONSTRAINTS:
BULK INSERT AdventureWorks.dbo.Employees
FROM 'C:\Data\Employees.csv'
WITH
(
FIELDTERMINATOR = ',',
CHECK_CONSTRAINTS
)
Unlike the FIELDTERMINATOR option, the CHECK_CONSTRAINTS option takes no arguments. It simply tells the BULK INSERT statement to enforce all CHECK constraints on the table or view before inserting data. Otherwise, these constraints are not enforced. (Note that PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL constraints are always enforced.) If the CHECK_CONSTRAINTS option is included in the BULK INSERT statement, the statement will fail when a CHECK constraint is violated.
You may have noticed in the preceding example that, if you include multiple options in the WITH clause, you must separate these options with a comma. The following BULK INSERT statement, for example, builds on the previous example by adding the KEEPIDENTITY option:
BULK INSERT AdventureWorks.dbo.Employees
FROM 'C:\Data\Employees.csv'
WITH
(
FIELDTERMINATOR = ',',
CHECK_CONSTRAINTS,
KEEPIDENTITY
)
The KEEPIDENTITY option specifies that identity values should be preserved when inserted into the target table. If you were to run the previous examples, you might notice that new identity values were inserted for the EmployeeID column. By using KEEPIDENTITY, however, the EmployeeID values saved to the Employees.csv file will be used.
In addition to the data-focused options shown in the examples above, the BULK INSERT statement also supports options that control the batch load process. The following statement, for example, includes the BATCHSIZE option, which specifies the number of rows in a batch:
BULK INSERT AdventureWorks.dbo.Employees
FROM 'C:\Data\Employees.csv'
WITH
(
FIELDTERMINATOR = ',',
CHECK_CONSTRAINTS,
KEEPIDENTITY,
BATCHSIZE = 50
)
When you run this statement, the rows are inserted into the target table in batches of 50. You can verify this in the messages returned by the query. The preceding statement returns the following messages:
(50 row(s) affected)
(50 row(s) affected)
(50 row(s) affected)
(50 row(s) affected)
(50 row(s) affected)
(289 row(s) affected)
As you can see, the data is now loaded in 50 row batches, and the total number of rows is summarized in the last message.
As mentioned previously, the BULK INSERT statement doe SQL Server 2005 and 2008 supports many other options in addition to the those shown. You can find details about each option by referring to the BULK INSERT (Transact-SQL) topic in SQL Server Books Online.
The topic also discusses how to call a format file when bulk-loading your data. A format file is useful when the data file contains a different number of columns than does your target table: the columns are in a different order, the column delimiters vary or there are anomalies in the data format. For a basic BULK INSERT operation, however, the examples above should provide many of the details you need to get started loading data from various types of data files.
ABOUT THE AUTHOR:
[IMAGE]
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. His books include Beginning MySQL (part of the Wrox Programmer-to-Programmer series), SQL: A Beginner's Guide (based on the SQL:1999 standard), MCSE Training Kit: Designing Highly Available Web Solutions with Microsoft Windows 2000 Server Technologies, and MCSE Training Kit: Microsoft SQL Server 2000 Database Design and Implementation. You can find more information at http://www.rhsheldon.com.
