Bulk Import and Export of Data (SQL Server)

SQL Server supports exporting data in bulk (bulk data) from a SQL Server table and importing bulk data into a SQL Server table or nonpartitioned view.

Bulk exporting refers to copying data from a SQL Server table to a data file. Bulk importing refers to loading data from a data file into a SQL Server table. For example, you can export data from a Microsoft Excel application to a data file and then bulk import that data into a SQL Server table.

Methods for bulk importing and exporting data

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
bcp utility A command-line utility (Bcp.exe) that bulk exports and bulk imports data and generates format files. Yes Yes
BULK INSERT statement A Transact-SQL statement that imports data directly from a data file into a database table or nonpartitioned view. Yes No
INSERT ... SELECT * FROM OPENROWSET(BULK...) statement 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
SQL Server Import and Export Wizard The wizard creates simple packages that import and export data between many popular data formats including databases, spreadsheets, and text files. Yes Yes
Important

Comma-separated value (CSV) files are not supported by SQL Server bulk-import operations. However, in some cases you can use a CSV file as the data file for a bulk import of data into SQL Server. Note that the field terminator of a CSV file does not have to be a comma. For more information, see Prepare Data for Bulk Export or Import (SQL Server).

Note

Only the bcp utility is suppported by Azure SQL Database and Azure SQL DW for importing and exporting delimited files.

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 supports two kinds of format files: XML format files and non-XML format files.

The bcp utility is the only tool that can generate a format file. For more information, see Create a Format File (SQL Server). For more information about format files, see Format Files for Importing or Exporting Data (SQL Server).

Note

In cases when a format file is not supplied during a bulk export or import operations, you can override the default formatting at the command line.

Related Topics
Prepare Data for Bulk Export or Import (SQL Server)
Data Formats for Bulk Import or Bulk Export (SQL Server)
 ● Use Native Format to Import or Export Data (SQL Server)
 ● Use Character Format to Import or Export Data (SQL Server)
 ● Use Unicode Native Format to Import or Export Data (SQL Server)
 ● Use Unicode Character Format to Import or Export Data (SQL Server)
 ● Import Native and Character Format Data from Earlier Versions of SQL Server
Specify Data Formats for Compatibility when Using bcp (SQL Server)
 ● Specify File Storage Type by Using bcp (SQL Server)
 ● Specify Prefix Length in Data Files by Using bcp (SQL Server)
 ● Specify Field Length by Using bcp (SQL Server)
 ● Specify Field and Row Terminators (SQL Server)
Keep Nulls or Use Default Values During Bulk Import (SQL Server)
Keep Identity Values When Bulk Importing Data (SQL Server)
Format Files for Importing or Exporting Data (SQL Server)
 ● Create a Format File (SQL Server)
 ● Use a Format File to Bulk Import Data (SQL Server)
 ● Use a Format File to Skip a Table Column (SQL Server)
 ● Use a Format File to Skip a Data Field (SQL Server)
 ● Use a Format File to Map Table Columns to Data-File Fields (SQL Server)

More information!

Prerequisites for Minimal Logging in Bulk Import
Examples of Bulk Import and Export of XML Documents (SQL Server)
SQL Server Integration Services
Copy Databases to Other Servers
Performing Bulk Load of XML Data (SQLXML 4.0)
Performing Bulk Copy Operations