| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords | monikerRange | |||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
Import & export bulk data with bcp |
Use bcp to export data from anywhere in a SQL Server database that SELECT works. Bulk export data from a table or from a query and bulk import from a file. |
markingmyname |
maghan |
09/28/2016 |
sql |
data-movement |
conceptual |
|
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW]
This topic provides an overview for using the bcp utility to export data from anywhere in a [!INCLUDEssNoVersion] database where a SELECT statement works, including partitioned views.
The bcp utility (Bcp.exe) is a command-line tool that uses the Bulk Copy Program (BCP) API. The bcp utility performs the following tasks:
-
Bulk exports data from a [!INCLUDEssNoVersion] table into a data file.
-
Bulk exports data from a query.
-
Bulk imports data from a data file into a [!INCLUDEssNoVersion] table.
-
Generates format files.
The bcp utility is accessed by the bcp command. To use the bcp command to bulk import data, you must understand the schema of the table and the data types of its columns, unless you are using a pre-existing format file.
The bcp utility can export data from a [!INCLUDEssNoVersion] table to a data file for use in other programs. The utility can also import data into a [!INCLUDEssNoVersion] table from another program, usually another database management system (DBMS). The data is first exported from the source program to a data file and then, in a separate operation, copied from the data file into a [!INCLUDEssNoVersion] table.
The bcp command provides switches that you use to specify the data type of the data file and other information. If these switches are not specified, the command prompts for formatting information, such as the type of data fields in a data file. The command then asks whether you want to create a format file that contains your interactive responses. If you want flexibility for future bulk-import or bulk-export operations, a format file is often useful. You can specify the format file on later bcp commands for equivalent data files. For more information, see Specify Data Formats for Compatibility when Using bcp (SQL Server).
Note
The bcp utility is written by using the ODBC bulk-copy.
For a description of the bcp command syntax, see bcp Utility.
| The following topics contain examples of using bcp: |
|---|
| bcp Utility 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) 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) Examples of Bulk Import and Export of XML Documents (SQL Server)
|