bcp大容量复制实用工具_运行中的BCP（大容量复制程序）命令

最新推荐文章于 2023-09-03 05:29:50 发布

culuo4781

最新推荐文章于 2023-09-03 05:29:50 发布

阅读量779

点赞数

文章标签：数据库 python mysql java 大数据

原文链接：https://www.sqlshack.com/bcp-bulk-copy-program-command-in-action/

版权

bcp大容量复制实用工具

There are various methods available for bulk data operations.

有许多方法可用于批量数据操作。

BCP utility
BCP实用程序
BULK INSERT
批量插入
Using OPENROWSET
使用OPENROWSET
Import/Export wizard
导入/导出向导

The BCP (Bulk Copy Program) utility is a command line that program that bulk-copies data between a SQL instance and a data file using a special format file. The BCP utility can be used to import large numbers of rows into SQL Server or export SQL Server data into files. The BCP data files don’t include any schema details or format information. Hence, it is recommended to create a format file to record the data format so in case of any failures, you can refer to the format file and better understand the data format to determine what may have gone wrong..

BCP（大容量复制程序）实用程序是一个命令行，该程序使用特殊格式的文件对SQL实例和数据文件之间的数据进行大容量复制。 BCP实用程序可用于将大量行导入SQL Server或将SQL Server数据导出到文件中。 BCP数据文件不包含任何架构详细信息或格式信息。因此，建议创建一个格式文件来记录数据格式，以便在发生任何故障的情况下，您可以参考该格式文件并更好地了解该数据格式以确定可能出了什么问题。

We’ve been using the BCP tool for a long time, the reason being that it has a very low overhead, and works great for bulk exporting and importing of data. It is one of the most efficient ways to handle bulk import and export of data.

我们已经使用BCP工具很长时间了，原因是它的开销非常低，并且非常适合批量导出和导入数据。这是处理批量导入和导出数据的最有效方法之一。

Article overview

文章概述

In this article, the BCP utility will be explained in detail. It covers the following topics:

在本文中，将详细介绍BCP实用程序。它涵盖以下主题：

BCP import and export command
BCP导入和导出命令
The format file
格式文件
How to create format file
如何创建格式文件
How to use the format file for data management
如何使用格式文件进行数据管理
and more…
和更多…

Configuration

组态

SQL Server supports exporting of data from a SQL Server table and importing the same into another table. To run the BCP command in ssms, enable the xp_cmdshell server configuration parameter. This gives you the control to run extended stored procedures. Keep in mind, though, that this is not always a recommended option, since it exposes the SQL Server surface to potential threats from the outside world.

SQL Server支持从SQL Server表中导出数据并将其导入到另一个表中。要在ssms中运行BCP命令，请启用xp_cmdshell服务器配置参数。这使您可以控制运行扩展存储过程。但是请记住，这并不总是推荐的选择，因为它使SQL Server表面暴露于来自外部世界的潜在威胁。

EXEC master..xp_cmdshell 'BCP ProdSQLShackDemo.dbo.SQLShackAuthor OUT f:\PowerSQL\ProdSQLShackDemo.txt -T -c'

BCP IN | OUT | QUERYOUT选项 (BCP IN | OUT | QUERYOUT options)

OUT: This option is used to export (or dump) all the records from a table into a data file. For example,

OUT：此选项用于将表中的所有记录导出（或转储）到数据文件中。例如，

C:\WINDOWS\System32>BCP [AdventureWorks2014].[dbo].[SalesOrderDetail] out C:\SODetail_Out.txt -S hqdbt01\SQL2017 -T -c –b1000 –t,

SQLShackDemoATC.dbo.SQLShackDemo – This is the name of the table that we intend to export. We can also use -d option to include the database name.
SQLShackDemoATC.dbo.SQLShackDemo –这是我们要导出的表的名称。我们还可以使用-d选项包括数据库名称。
c :\SODetail_Out.txt – This is the output file where the data is dumped to
c：\ SODetail_Out.txt –这是将数据转储到的输出文件
-T – Trusted Windows authentication
-T –可信的Windows身份验证
-t, – define comma as the field separator
-t，–将逗号定义为字段分隔符
-w – Use wide width data format
-w –使用宽数据格式
-b1000 – Export the data in batches of 1000 rows
-b1000 –批量导出1000行数据

IN: This option is used to import all the records to an existing table. This requires the table to be created before executing the BCP command.

IN：此选项用于将所有记录导入到现有表中。这要求在执行BCP命令之前先创建表。

Let’s create a table to import the data, called SalesOrderDetailsIn using the following create statement

让我们使用以下create语句创建一个表以导入数据，称为SalesOrderDetailsIn

USE [AdventureWorks2014]
GO
CREATE TABLE [dbo].[SalesOrderDetailIn](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

We can see that in the following BCP command that the IN keyword is used to import the data from the SODetails_Output.txt file.

我们可以看到，在下面的BCP命令中，IN关键字用于从SODetails_Output.txt文件导入数据。

C:\WINDOWS\System32>BCP [adventureworks2014].dbo.[SalesOrderDetailIn] in C:\SODetail_Output.txt -S hqdbt01\SQL2017 -T –c

BCP QUERYOUT

BCP查询

In the following example, we’re not going to export an entire table. Let’s look at a way to run a SQL statement to generate a data dump. The BCP export command is used in conjunction with the SELECT statement and the queryout option. Using queryout the SQL statement can be run on a defined connection and the data can be dumped into another file; the other switches are the same in this example as well.

在下面的示例中，我们将不会导出整个表。让我们看一种运行SQL语句以生成数据转储的方法。 BCP导出命令与SELECT语句和queryout选项一起使用。使用queryout可以在定义的连接上运行SQL语句，并且可以将数据转储到另一个文件中。其他开关在此示例中也相同。

BCP "SELECT [SalesOrderID], [SalesOrderDetailID],[CarrierTrackingNumber] FROM [AdventureWorks2014].[dbo].[SalesOrderDetail]" queryout C:\SOQueryOut.txt -S hqdbt01\SQL2017 -T –c
 
CREATE TABLE [dbo].[SalesOrderDetailQueryOut](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL
)
 
C:\WINDOWS\System32>BCP [adventureworks2014].dbo.[SalesOrderDetailQueryOut] in C:\SOQueryOut.txt -S hqdbt01\SQL2017 -T -c

格式化文件 (Format files)

The BCP utility supports the use of a format file that contains the formatting details of each field in a file. The format file is used to provide all the required formatting details for bulk export and bulk import operations.

BCP实用程序支持使用格式文件，该文件包含文件中每个字段的格式详细信息。格式文件用于为批量导出和批量导入操作提供所有必需的格式详细信息。

It provides a flexible way to interpret the data.
它提供了一种灵活的方式来解释数据。
It provides an interface to re-format the data during the export process.
它提供了在导出过程中重新格式化数据的界面。
The format file eliminates the need of special programs for data import and export operations.
格式文件消除了用于数据导入和导出操作的特殊程序的需要。

SQL Server supports two kinds of format files: XML format files and non-XML format files. The non-XML format file is the original format supported by earlier versions of SQL Server.

SQL Server支持两种格式文件：XML格式文件和非XML格式文件。非XML格式文件是SQL Server早期版本支持的原始格式。

In this section we shall walk through some of the concepts of the format file, look at how to create the format file, and other details.

在本节中，我们将逐步介绍格式文件的一些概念，研究如何创建格式文件以及其他细节。

Let’s Jump in and get started:

让我们开始吧：

CREATE TABLE SQLShackDemo
   (   
   EID smallint,   
   EName nvarchar(50) NULL,   
   EJOB nvarchar(50) NOT NULL   
   );   
GO   
INSERT INTO SQLShackDemo values(1,'Prashanth','DB Manager'),(2,'Bob','IT Director'),(3,'Scott','DevOp Eng');

创建非XML格式的文件 (Creating a Non-XML Format File)

A non-XML format file is an ordinary file with a well-defined structure, and contains metadata about the column such as storage type, prefix length, field length, and field terminator.

非XML格式的文件是具有良好定义的结构的普通文件，并且包含有关列的元数据，例如存储类型，前缀长度，字段长度和字段终止符。

The following BCP command is used to create format file:

以下BCP命令用于创建格式文件：

BCP TestDatabase.dbo. SQLShackDemo   format nul -c -f c:\BCPImport.fmt -t, -T

Version 10.0 is the version number of the BCP utility.
版本10.0是BCP实用程序的版本号。
Number of columns is the number of fields in the data file; in this case, it’s 3.
列数是数据文件中的字段数；在这种情况下为3。
The other fields in the format file describe the nature of the data fields.
格式文件中的其他字段描述了数据字段的性质。
Field Order indicates the position of each field.
字段顺序指示每个字段的位置。
Prefix Len is the length for the prefix characters.
前缀Len是前缀字符的长度。
Data Len is the maximum allowed length for the data for the specific field.
数据长度是特定字段数据的最大允许长度。
Terminator is the delimiter used to separate any two fields of the data.
终结符是用于分隔数据的任何两个字段的定界符。
Column Order is the SQL Server table column order.
列顺序是SQL Server表的列顺序。
Column Name is the name of the SQL Server table column.
列名是SQL Server表列的名称。
Column Collation is the collation used to store the characters in the data file.
列排序规则是用于将字符存储在数据文件中的排序规则。

If -f is used with the format option, the specified format file is created for the specified table or view. To create an XML format file, specify the -x option.

如果-f与format选项一起使用，则将为指定的表或视图创建指定的格式文件。要创建XML格式的文件，请指定-x选项。

Let’s see an example to skip ENAME column of a table and load the data into the SQLShackDemoSkip table using a format file. Before changing the format file, let’s create the SQLShackDemoSkip table and a data file with EID and EJOB data using the SQLShackDemo table.

让我们看一个示例，该示例跳过表的ENAME列，并使用格式文件将数据加载到SQLShackDemoSkip表中。在更改格式文件之前，让我们使用SQLShackDemo表创建SQLShackDemoSkip表以及具有EID和EJOB数据的数据文件。

CREATE TABLE SQLShackDemoSkip   
   (   
   EID smallint,   
   EName nvarchar(50) NULL,   
   EJOB nvarchar(50) NOT NULL   
   );

Run the BCP command to generate the data file:

运行BCP命令以生成数据文件：

C:\WINDOWS\System32>BCP "select EID,EJOB from SQLShackDEMOATC.dbo.SQLShackDemo" queryout c:\SQLShackDemo3.txt -T -S HQDBT01\SQL2017 -c -t,

Following is the output of the data file:

以下是数据文件的输出：

Now, modify the format file, the first data field maps to EID, skips ENAME, and the third row maps the second data field of the data file to EJOB. The loading should ignore the ENAME column.

现在，修改格式文件，第一个数据字段映射到EID，跳过ENAME，第三行将数据文件的第二个数据字段映射到EJOB。加载应忽略ENAME列。

To skip a table column, modify the format file definition of the corresponding row and set the values to 0 for all the related columns as shown below.

要跳过表列，请修改相应行的格式文件定义，并将所有相关列的值设置为0，如下所示。

Run the following BCP command to load the data into the SQLShackDemoSkip table.

运行以下BCP命令以将数据加载到SQLShackDemoSkip表中。

C:\WINDOWS\System32>BCP SQLShackDemoATC.dbo.SQLShackDemoSkip in C:\SQLShackDemo3.txt -f C:\BCPformat.fmt  -S hqdbt01\SQL2017 –T

The loading process excludes the second column of SQlShackDemoSkip.

加载过程不包括SQlShackDemoSkip的第二列。

SELECT * FROM SQLShackDemoSkip;

摘要 (Summary)

The BCP Utility, that’s very familiar to all SQL Server administrators, operates really well and is fast as well as efficient in terms of data import and export. Most of the options in the BCP command are case sensitive. It is recommended that we assume everything as case sensitive, so that we never run into troubles using the command.

所有SQL Server管理员都非常熟悉的BCP实用程序运行得很好，并且在数据导入和导出方面既快速又高效。 BCP命令中的大多数选项都区分大小写。建议我们假设所有内容都区分大小写，以免使用该命令造成麻烦。

BCP is capable of exporting and importing really large chunks of data, but if you’re exporting/importing data in the range of tens of millions, it’s recommended to break it into smaller chunks. And this can be using the SELECT statement with the WHERE clause. When we’re bringing the data in, we need to be cautious of the defined constraints. Sometimes we need to delete the constraint and dump the data, and then re-add those deleted constraints.

BCP能够导出和导入非常大的数据块，但是如果要导出/导入数千万的数据，建议将其分成较小的块。这可以与WHERE子句一起使用SELECT语句。当我们引入数据时，我们需要注意定义的约束。有时我们需要删除约束并转储数据，然后重新添加那些删除的约束。