Bulk import and export of data (SQL Server)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL database in Microsoft Fabric

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 bulk importing data into a SQL Server table or nonpartitioned view. The following basic methods are available.

For rules about using a comma-separated value (CSV) file as the data file for a bulk import of data into SQL Server, see Prepare data for bulk export or import.

Format files

The bcp utility, BULK INSERT, and 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. Use the format file to provide all the format information 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's in the data file during import, and also to format data in the data file during export. This flexibility removes the need to write special-purpose code to interpret the data or reformat it to the specific requirements of SQL Server or the external application. For example, if you're bulk exporting data to be loaded into an application that requires comma-separated values, use a format file to insert commas as field terminators in the exported data.

SQL Server supports two types 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 with bcp. For more information about format files, see Format files to import or export data.

Related articles

• Prepare data for bulk export or import

• Data formats for bulk import or bulk export

  • Use native format to import or export data
  • Use character format to import or export data
  • Use Unicode Native Format to Import or Export Data
  • Use Unicode character format to import or export data
  • Import native and character format data from earlier versions of SQL Server

• Specify compatibility data formats when using bcp

  • Specify file storage type using bcp
  • Specify prefix length in data files using bcp
  • Specify field length by using bcp
  • Specify field and row terminators

• Keep nulls or default values during bulk import

• Keep identity values when bulk importing data

• Format files to import or export data

  • Create a format file with bcp
  • Use a format file to bulk import data
  • Use a format file to skip a table column
  • Use a format file to skip a data field
  • Use a format file to map table columns to data-file fields

Related content

• 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 in SQL Server Native Client