linux中的数据库复制粘贴,将数据批量复制到 Linux 上的 SQL Server - SQL Server | Microsoft Docs...

使用 bcp 将数据批量复制到 Linux 上的 SQL ServerBulk copy data with bcp to SQL Server on Linux

01/30/2018

本文内容

适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) - Linux适用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) - Linux

本文介绍如何使用 bcp 命令行实用工具，在 Linux 上的 SQL Server 实例和采用用户指定格式的数据文件之间批量复制数据。This article shows how to use the bcp command-line utility to bulk copy data between an instance of SQL Server on Linux and a data file in a user-specified format.

可以使用 bcp 将许多行导入 SQL Server 表中，或将数据从 SQL Server 表导出到数据文件。You can use bcp to import large numbers of rows into SQL Server tables or to export data from SQL Server tables into data files. 除非与 queryout 选项配合使用，否则使用 bcp 不需要了解 Transact-SQL 知识。Except when used with the queryout option, bcp requires no knowledge of Transact-SQL. bcp 命令行实用工具可用于在本地或云中、在 Linux、Windows 或 Docker 上以及在 Azure SQL 数据库和 Azure Synapse Analytics 中运行的 Microsoft SQL Server。The bcp command-line utility works with Microsoft SQL Server running on-premises or in the cloud, on Linux, Windows or Docker and Azure SQL Database and Azure Synapse Analytics.

本文介绍如何：This article shows you how to:

使用 bcp in 命令将数据导入表Import data into a table using the bcp in command

使用 bcp out 命令从表中导出数据Export data from a table using the bcp out command

安装 SQL Server 命令行工具Install the SQL Server command-line tools

bcp 是 SQL Server 命令行工具的一部分，不会随 Linux 上的 SQL Server 自动安装。bcp is part of the SQL Server command-line tools, which are not installed automatically with SQL Server on Linux. 如果尚未在 Linux 计算机上安装 SQL Server 命令行工具，则必须先安装它们。If you have not already installed the SQL Server command-line tools on your Linux machine, you must install them. 有关如何安装这些工具的详细信息，请从以下列表中选择 Linux 分发版：For more information on how to install the tools, select your Linux distribution from the following list:

使用 bcp 导入数据Import data with bcp

在本教程中，将在本地 SQL Server 实例 (localhost) 上创建示例数据库和表，然后使用 bcp 从磁盘上的文本文件加载到示例表。In this tutorial, you create a sample database and table on the local SQL Server instance (localhost) and then use bcp to load into the sample table from a text file on disk.

创建示例数据库和表Create a sample database and table

首先创建一个具有简单表的示例数据库，本教程接下来会使用该数据库。Let's start by creating a sample database with a simple table that is used in the rest of this tutorial.

在 Linux 框中，打开命令终端。On your Linux box, open a command terminal.

将以下命令复制并粘贴到终端窗口中。Copy and paste the following commands into the terminal window. 这些命令使用 sqlcmd 命令行实用工具在本地 SQL Server 实例 (localhost) 上创建示例数据库 (BcpSampleDB) 和表 (TestEmployees)。These commands use the sqlcmd command-line utility to create a sample database (BcpSampleDB) and a table (TestEmployees) on the local SQL Server instance (localhost). 运行命令前，请记得根据需要替换 username 和 。Remember to replace the username and as necessary before running the commands.

创建数据库 BcpSampleDB：Create the database BcpSampleDB:

sqlcmd -S localhost -U sa -P -Q "CREATE DATABASE BcpSampleDB;"

在数据库 BcpSampleDB 中创建表 TestEmployees：Create the table TestEmployees in the database BcpSampleDB:

sqlcmd -S localhost -U sa -P -d BcpSampleDB -Q "CREATE TABLE TestEmployees (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));"

创建源数据文件Create the source data file

将以下命令复制并粘贴到终端窗口中。Copy and paste the following command into your terminal window. 我们使用内置的 cat 命令创建包含三条记录的示例文本数据文件，并将文件作为 ~/test_data.txt 保存在主目录中。We use the built-in cat command to create a sample text data file with three records save the file in your home directory as ~/test_data.txt. 记录中的字段用逗号分隔。The fields in the records are delimited by a comma.

cat > ~/test_data.txt << EOF

1,Jared,Australia

2,Nikita,India

3,Tom,Germany

EOF

可通过在终端窗口中运行以下命令，验证是否已正确创建数据文件：You can verify that the data file was created correctly by running the following command in your terminal window:

cat ~/test_data.txt

终端窗口中应该会显示以下内容：This should display the following in your terminal window:

1,Jared,Australia

2,Nikita,India

3,Tom,Germany

从源数据文件导入数据Import data from the source data file

将以下命令复制并粘贴到终端窗口中。Copy and paste the following commands into the terminal window. 此命令使用 bcp 连接到本地 SQL Server 实例 (localhost)，并将数据文件 ( ~/test_data.txt) 中的数据导入数据库 (BcpSampleDB) 中的表 (TestEmployees)。This command uses bcp to connect to the local SQL Server instance (localhost) and import the data from the data file (~/test_data.txt) into the table (TestEmployees) in the database (BcpSampleDB). 运行命令前，请记得根据需要替换用户名和 。Remember to replace the username and as necessary before running the commands.

bcp TestEmployees in ~/test_data.txt -S localhost -U sa -P -d BcpSampleDB -c -t ','

以下是此示例中与 bcp 配合使用的命令行参数的简要概述：Here's a brief overview of the command-line parameters we used with bcp in this example:

-S：指定要连接到的 SQL Server 实例-S: specifies the instance of SQL Server to which to connect

-U：指定用于连接到 SQL Server 的登录 ID-U: specifies the login ID used to connect to SQL Server

-P：指定登录 ID 的密码-P: specifies the password for the login ID

-d：指定要连接到的数据库-d: specifies the database to connect to

-c：使用字符数据类型执行操作-c: performs operations using a character data type

-t：指定字段终止符。-t: specifies the field terminator. 我们在数据文件中使用 comma 作为记录的字段终止符We are using comma as the field terminator for the records in our data file

备注

本示例中不指定自定义行终止符。We are not specifying a custom row terminator in this example. 先前使用 cat 命令创建数据文件时，文本数据文件中的行已使用 newline 正确终止。Rows in the text data file were correctly terminated with newline when we used the cat command to create the data file earlier.

可通过在终端窗口中运行以下命令，验证是否已成功导入数据。You can verify that the data was successfully imported by running the following command in your terminal window. 运行命令前，请记得根据需要替换 username 和 。Remember to replace the username and as necessary before running the command.

sqlcmd -S localhost -d BcpSampleDB -U sa -P -I -Q "SELECT * FROM TestEmployees;"

应该会显示以下结果：This should display the following results:

Id Name Location

----------- ------------------- -------------------

1 Jared Australia

2 Nikita India

3 Tom Germany

(3 rows affected)

使用 bcp 导出数据Export data with bcp

在本教程中，将使用 bcp 把先前创建的示例表中的数据导出到新数据文件。In this tutorial, you use bcp to export data from the sample table we created earlier to a new data file.

将以下命令复制并粘贴到终端窗口中。Copy and paste the following commands into the terminal window. 这些命令使用 bcp 命令行实用工具，将数据库 BcpSampleDB 中表 TestEmployees 的数据导出到名为 ~/test_export.txt 的新数据文件。These commands use the bcp command-line utility to export data from the table TestEmployees in the database BcpSampleDB to a new data file called ~/test_export.txt. 运行命令前，请记得根据需要替换用户名和 。Remember to replace the username and as necessary before running the command.

bcp TestEmployees out ~/test_export.txt -S localhost -U sa -P -d BcpSampleDB -c -t ','

可通过在终端窗口中运行以下命令，验证是否已正确导出数据：You can verify that the data was exported correctly by running the following command in your terminal window:

cat ~/test_export.txt

终端窗口中应该会显示以下内容：This should display the following in your terminal window:

1,Jared,Australia

2,Nikita,India

3,Tom,Germany

另请参阅See also