--- title: "Bulk Import and Export of Data (SQL Server) | Microsoft Docs" ms.custom: "" ms.date: "06/14/2017" ms.prod: "sql-server-2014" ms.reviewer: "" ms.suite: "" ms.technology: - "dbe-bulk-import-export" ms.tgt_pltfrm: "" ms.topic: "article" helpviewer_keywords: - "exporting data" - "bulk importing [SQL Server], about bulk importing" - "data [SQL Server], bulk export and import" - "transferring data" - "importing data, (See also bulk importing [SQL Server])" - "copying data [SQL Server]" - "bulk exporting [SQL Server]" - "importing data, bulk import" - "copying data [SQL Server], bulk export and import" - "exporting data,(See also bulk exporting [SQL Server])" - "bulk exporting [SQL Server], about bulk exporting" - "bulk importing [SQL Server]" - "importing data" ms.assetid: 19049021-c048-44a2-b38d-186d9f9e4a65 caps.latest.revision: 56 author: "JennieHubbard" ms.author: "jhubbard" manager: "jhubbard" --- # Bulk Import and Export of Data (SQL Server) [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] supports exporting data in bulk (*bulk data*) from a [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] table and importing bulk data into a [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] table or nonpartitioned view. Bulk importing and bulk exporting are essential to efficient transfer data between [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] and heterogeneous data sources. *Bulk exporting* refers to copying data from a [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] table to a data file. *Bulk importing* refers to loading data from a data file into a [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] table. For example, you can export data from a [!INCLUDE[msCoName](../includes/msconame-md.md)] Excel application to a data file and then bulk import that data into a [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] table. **In this Topic:** - [Introduction to Bulk Import and Bulk Export Operations](#Intro) - [Related Tasks](#RelatedTasks) ## Bulk Import and Bulk Export Overview This section lists and briefly compares the various methods that are available for bulk importing and exporting data. The section also introduces format files. **In This Topic:** - [Methods for Bulk Importing and Exporting Data](#MethodsForBuliIE) - [Format Files](#FFs) ### Methods for Bulk Importing and Exporting Data [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] supports bulk exporting data from a [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] table and for bulk importing data into a [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] table or nonpartitioned view. The following basic methods are available. |Method|Description|Imports data|Exports data| |------------|-----------------|------------------|------------------| |[bcp utility](../../2014/database-engine/import-and-export-bulk-data-by-using-the-bcp-utility-sql-server.md)|A command-line utility (Bcp.exe) that bulk exports and bulk imports data and generates format files.|Yes|Yes| |[BULK INSERT statement](../../2014/database-engine/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server.md)|A [!INCLUDE[tsql](../includes/tsql-md.md)] statement that imports data directly from a data file into a database table or nonpartitioned view.|Yes|No| |[INSERT ... SELECT * FROM OPENROWSET(BULK...) statement](../../2014/database-engine/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server.md)|A [!INCLUDE[tsql](../includes/tsql-md.md)] statement that uses the OPENROWSET bulk rowset provider to bulk import data into a [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] table by specifying the OPENROWSET(BULK…) function to select data in an INSERT statement.|Yes|No| > [!IMPORTANT] > Comma-separated value (CSV) files are not supported by SQL Server bulk-import operations. However, in some cases, a CSV file can be used 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)](../../2014/database-engine/prepare-data-for-bulk-export-or-import-sql-server.md). ### 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 [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] 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 [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)]. 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 [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] 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. [!INCLUDE[ssNoVersion](../includes/ssnoversion-md.md)] 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)](../../2014/database-engine/create-a-format-file-sql-server.md). For more information about format files, see [Format Files for Importing or Exporting Data (SQL Server)](../../2014/database-engine/format-files-for-importing-or-exporting-data-sql-server.md). > [!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 Tasks - [Import and Export Bulk Data by Using the bcp Utility (SQL Server)](../../2014/database-engine/import-and-export-bulk-data-by-using-the-bcp-utility-sql-server.md) - [Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server)](../../2014/database-engine/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server.md) - [Keep Identity Values When Bulk Importing Data (SQL Server)](../../2014/database-engine/keep-identity-values-when-bulk-importing-data-sql-server.md) - [Keep Nulls or Use Default Values During Bulk Import (SQL Server)](../../2014/database-engine/keep-nulls-or-use-default-values-during-bulk-import-sql-server.md) - [Prepare Data for Bulk Export or Import (SQL Server)](../../2014/database-engine/prepare-data-for-bulk-export-or-import-sql-server.md) **To use a format file** - [Create a Format File (SQL Server)](../../2014/database-engine/create-a-format-file-sql-server.md) - [Use a Format File to Bulk Import Data (SQL Server)](../../2014/database-engine/use-a-format-file-to-bulk-import-data-sql-server.md) - [Use a Format File to Map Table Columns to Data-File Fields (SQL Server)](../../2014/database-engine/use-a-format-file-to-map-table-columns-to-data-file-fields-sql-server.md) - [Use a Format File to Skip a Data Field (SQL Server)](../../2014/database-engine/use-a-format-file-to-skip-a-data-field-sql-server.md) - [Use a Format File to Skip a Table Column (SQL Server)](../../2014/database-engine/use-a-format-file-to-skip-a-table-column-sql-server.md) **To use data formats for bulk import or bulk export** - [Import Native and Character Format Data from Earlier Versions of SQL Server](../../2014/database-engine/import-native-and-character-format-data-from-earlier-versions-of-sql-server.md) - [Use Character Format to Import or Export Data (SQL Server)](../../2014/database-engine/use-character-format-to-import-or-export-data-sql-server.md) - [Use Native Format to Import or Export Data (SQL Server)](../../2014/database-engine/use-native-format-to-import-or-export-data-sql-server.md) - [Use Unicode Character Format to Import or Export Data (SQL Server)](../../2014/database-engine/use-unicode-character-format-to-import-or-export-data-sql-server.md) - [Use Unicode Native Format to Import or Export Data (SQL Server)](../../2014/database-engine/use-unicode-native-format-to-import-or-export-data-sql-server.md) **To specify data formats for compatibility when using bcp** 1. [Specify Field and Row Terminators (SQL Server)](../../2014/database-engine/specify-field-and-row-terminators-sql-server.md) 2. [Specify Prefix Length in Data Files by Using bcp (SQL Server)](../../2014/database-engine/specify-prefix-length-in-data-files-by-using-bcp-sql-server.md) 3. [Specify File Storage Type by Using bcp (SQL Server)](../../2014/database-engine/specify-file-storage-type-by-using-bcp-sql-server.md) ## See Also [Prerequisites for Minimal Logging in Bulk Import](../../2014/database-engine/prerequisites-for-minimal-logging-in-bulk-import.md) [Format Files for Importing or Exporting Data (SQL Server)](../../2014/database-engine/format-files-for-importing-or-exporting-data-sql-server.md) [Examples of Bulk Import and Export of XML Documents (SQL Server)](../../2014/database-engine/examples-of-bulk-import-and-export-of-xml-documents-sql-server.md) [SQL Server Integration Services](../../2014/integration-services/sql-server-integration-services.md) [Copy Databases to Other Servers](../../2014/database-engine/copy-databases-to-other-servers.md) [Performing Bulk Load of XML Data (SQLXML 4.0)](../../2014/database-engine/dev-guide/performing-bulk-load-of-xml-data-sqlxml-4-0.md) [Performing Bulk Copy Operations](../../2014/database-engine/dev-guide/performing-bulk-copy-operations.md) [bcp Utility](../../2014/database-engine/bcp-utility.md) [BULK INSERT (Transact-SQL)](~/t-sql/statements/bulk-insert-transact-sql.md) [Format Files for Importing or Exporting Data (SQL Server)](../../2014/database-engine/format-files-for-importing-or-exporting-data-sql-server.md) [OPENROWSET (Transact-SQL)](~/t-sql/functions/openrowset-transact-sql.md)