About Bulk Import and Bulk Export Operations
SQL Server supports bulk exporting data from a SQL Server table and for bulk importing data into a SQL Server table or nonpartitioned view. The following basic methods are available.
Method |
Description |
Imports data |
Exports data |
|---|---|---|---|
A command-line utility (Bcp.exe) that bulk exports and bulk imports data and generates format files. |
Yes |
Yes |
|
A Transact-SQL statement that imports data directly from a data file into a database table or nonpartitioned view. |
Yes |
No |
|
A Transact-SQL statement that uses the OPENROWSET bulk rowset provider to bulk import data into a SQL Server table by specifying the OPENROWSET(BULK…) function to select data in an INSERT statement. |
Yes |
No |
Restrictions
SQL Server bulk-import operations do not support importing data from comma-separated value (CSV) files. However, On 32-bit systems, it is possible to import CSV data into a SQL Server table without bulk-import optimizations by using OPENROWSET with the OLE DB Provider for Jet. Jet treats text files as tables, with the schema defined by a schema.ini file that is located in the same directory as the data source. For a CSV data, one of the parameters in the schema.ini file would be "FORMAT=CSVDelimited". To use this solution, you would need to understand how the Jet Test IISAMm operations—its connection string syntax, schema.ini usage, registry setting options, and so on). The best sources of this information are Microsoft Access Help and knowledge base (KB) articles. For more information, see Initializing the Text Data Source Driver, How To Use a SQL Server 7.0 Distributed Query with a Linked Server to Secured Access Databases, and HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases, How To Open Delimited Text Files Using the Jet Provider's Text IIsam.
In-Process vs. Out-of-Process Operation
The BULK INSERT statement and OPENROWSET(BULK) function execute in-process with SQL Server, sharing the same memory address space. Because the data files are opened by a SQL Server process, data is not copied between client process and SQL Server processes. For security considerations when importing data by using BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK...), see Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...).
In contrast, the bcp utility runs out-of-process. To move data across process memory spaces, bcp must use inter-process data marshaling. Inter-process data marshalingis the process of converting parameters of a method call into a stream of bytes. This can add significant load to the processor. However, because both bcp parses the data and convert data into native storage format in the client process, they can offload parsing and data conversion from the SQL Server process. Therefore, if you have a CPU constraint, you may achieve better bulk-import performance on a computer that has more than one CPU or on different computers, by using bcp instead of by using BULK INSERT or INSERT ... SELECT * FROM OPENROWSET(BULK).
Format Files
The bcp utility, BULK INSERT, and INSERT ... SELECT * FROM OPENROWSET(BULK...) all support the use of a specialized format file that stores format information for each field in a data file. A format file might also contain information about the corresponding SQL Server table. The format file can be used to provide all the format information that is required to bulk export data from and bulk import data to an instance of SQL Server.
Format files provide a flexible way to interpret data as it is in the data file during import, and also to format data in the data file during export. This flexibility eliminates the need to write special-purpose code to interpret the data or reformat the data to the specific requirements of SQL Server or the external application. For example, if you are bulk exporting data to be loaded into an application that requires comma-separated values, you can use a format file to insert commas as field terminators in the exported data.
SQL Server 2005 and later versions support two kinds of format files: XML format files and non-XML format files. Non-XML format files are supported by earlier versions of SQL Server; XML format files were new in SQL Server 2005.
The bcp utility is the only tool that can generate a format file. For more information, see Creating a Format File. For more information about format files, see Format Files for Importing or Exporting Data.
Note
In cases when a format file is not supplied during a bulk export or import operations, the user can choose to override the default formatting at the command line.
The Query Processor and Bulk Import
To bulk import data into an instance of SQL Server, the bcp utility, BULK INSERT statement, and INSERT ... SELECT * FROM OPENROWSET(BULK...) statement all work with the query processor.
All three methods convert the data in a data file into OLE DB rowsets. But the conversion method varies, as follows:
The bcp utility reads the data file and sends a TDS stream to the SQL Server Bulk Copy Program (BCP) API, which converts the data into OLE DB rowsets.
BULK INSERT and the OPENROWSET bulk rowset provider both convert a file data directly into an OLE DB rowset.
The OLE DB rowsets are inserted into the target table by the query processor, which plans and optimizes each operation.
Performance Considerations
Performance considerations can also be significant when large amounts of data are being imported. In some cases, performance can be improved by changing how a bulk-import or bulk-export operation handles one or more of the following:
Batch switches
Constraint checking of CHECK constraints
How bulk transactions are logged. This is relevant for databases that typically use the full recovery model.
Ordering exported data
Parallel data importing
Table locking
Trigger execution
For more information, see Optimizing Bulk Import Performance.
Note
No special optimization techniques exist for bulk-export operations. These operations simply select the data from the source table by using a SELECT statement.
See Also