使用 SQL Server 导入和导出向导导入和导出数据Import and Export Data with the SQL Server Import and Export Wizard

适用对象:是SQL Server,包含 Linux 版 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

利用SQL ServerSQL Server 导入和导出向导,可轻松将数据从源复制到目标。SQL ServerSQL Server Import and Export Wizard is a simple way to copy data from a source to a destination. 本概述介绍向导可用作源和目标的数据源,以及运行向导所需的权限。This overview describes the data sources that the wizard can use as sources and destinations, as well as the permissions you need to run the wizard.

获取向导Get the wizard

如果想要运行向导,但是尚未在计算机上安装 MicrosoftMicrosoft SQL ServerSQL Server ,则可以通过安装 SQL Server Data Tools (SSDT) 来安装 SQL ServerSQL Server 导入和导出向导。If you want to run the wizard, but you don't have MicrosoftMicrosoft SQL ServerSQL Server installed on your computer, you can install the SQL ServerSQL Server Import and Export Wizard by installing SQL Server Data Tools (SSDT). 有关详细信息,请参阅 下载 SQL Server Data Tools (SSDT)For more info, see Download SQL Server Data Tools (SSDT).

运行向导时会发生什么情况?What happens when I run the wizard?

我可以使用哪些源和目标?What sources and destinations can I use?

SQL ServerSQL Server 导入和导出向导可以将数据复制到下表中列出的数据源以及从中复制数据。The SQL ServerSQL Server Import and Export Wizard can copy data to and from the data sources listed in the following table. 若要连接其中某些数据源,可能需要下载并安装其他文件。To connect to some of these data sources, you may have to download and install additional files.

数据源Data source 是否必须下载其他文件?Do I have to download additional files?
企业数据库Enterprise databases
SQL ServerSQL Server、Oracle、DB2 等。, Oracle, DB2, and others.
SQL Server 或 SQL Server Data Tools (SSDT) 安装连接到 SQL ServerSQL Server 时所需的文件。SQL Server or SQL Server Data Tools (SSDT) installs the files that you need to connect to SQL ServerSQL Server. 但 SSDT 不安装连接到其他企业数据库(如 Oracle 或 IBM DB2)时所需的所有文件。But SSDT doesn't install all the files that you need to connect to other enterprise databases such as Oracle or IBM DB2.

若要连接企业数据库,通常需要具有以下两项内容:To connect to an enterprise database, you typically have to have two things:

1.客户端软件1. Client software. 如果已经为企业数据库系统安装了客户端软件,则通常会有建立连接所需的文件。If you already have the client software installed for your enterprise database system, then you typically have what you need to make a connection. 如果尚未安装客户端软件,请询问数据库管理员如何安装获得许可的副本。If you don't have the client software installed, ask the database administrator how to install a licensed copy.

2.驱动程序或提供程序2. Drivers or providers. Microsoft 安装用于连接 Oracle 的驱动程序和提供程序。Microsoft installs drivers and providers to connect to Oracle. 若要连接到 IBM DB2,请从 Microsoft SQL Server 2016 功能包中获取用于 Microsoft SQL Server 的 DB2 v5.0 的 Microsoft® OLEDB 提供程序。To connect to IBM DB2, get the Microsoft® OLEDB Provider for DB2 v5.0 for Microsoft SQL Server from the Microsoft SQL Server 2016 Feature Pack.

有关详细信息,请参阅连接到 SQL Server 数据源连接到 Oracle 数据源For more info, see Connect to a SQL Server Data Source or Connect to an Oracle Data Source.
文本文件(平面文件)Text files (flat files) 无需任何其他文件。No additional files required.

有关详细信息,请参阅连接到平面文件数据源For more info, see Connect to a Flat File Data Source.
Microsoft Excel 和 Microsoft Access 文件Microsoft Excel and Microsoft Access files Microsoft Office 并不会安装连接到作为数据源的 Excel 和 Access 文件所需的所有文件。Microsoft Office doesn't install all the files that you need to connect to Excel and Access files as data sources. 获取下载:Microsoft Access 数据库引擎 2016 可再发行组件Get the following download - Microsoft Access Database Engine 2016 Redistributable.

有关详细信息,请参阅连接到 Excel 数据源连接到 Access 数据源For more info, see Connect to an Excel Data Source or Connect to an Access Data Source.
Azure 数据源Azure data sources
目前仅限 Azure Blob 存储。Currently only Azure Blob Storage.
SQL Server Data Tools 不会安装需要作为数据源连接到 Azure Blob 存储的文件。SQL Server Data Tools don't install the files that you need to connect to Azure Blob Storage as a data source. 获取以下下载 — 用于 Azure 的 Microsoft SQL Server 2016 Integration Services 功能包Get the following download - Microsoft SQL Server 2016 Integration Services Feature Pack for Azure.

有关详细信息,请参阅连接到 Azure Blob 存储For more info, see Connect to Azure Blob Storage.
打开源数据库Open source databases
PostgreSQL、MySql 等。PostgreSQL, MySql, and others.
必须下载其他文件才能连接到这些数据源。To connect to these data sources, you have to download additional files.

- 对于 PostgreSQL,请参阅连接到 PostgreSQL 数据源- For PostgreSQL, see Connect to a PostgreSQL Data Source.
- 对于 MySql,请参阅连接到 MySQL 数据源- For MySql, see Connect to a MySQL Data Source.
提供驱动程序或提供程序的其他任何数据源Any other data source for which a driver or provider is available 通常必须下载其他文件才能连接到以下类型的数据源。You typically have to download additional files to connect to the following types of data sources.

- 提供 ODBC 驱动程序 的任何源。- Any source for which an ODBC driver is available. 有关详细信息,请参阅连接到 ODBC 数据源For more info, see Connect to an ODBC Data Source.
- 提供 .Net Framework 数据提供程序 的任何源。- Any source for which a .Net Framework Data Provider is available.
- 提供 OLE DB 提供程序 的任何源。- Any source for which an OLE DB Provider is available.

有时,为其他数据源提供源和目标功能的第三方组件被标记为 SQL Server Integration Services (SSIS) 的附加产品。Third-party components that provide source and destination capabilities for other data sources are sometimes marketed as add-on products for SQL Server Integration Services (SSIS).

如何连接到我的数据?How do I connect to my data?

有关如何连接到常用数据源的信息,请参阅以下页面之一:For info about how to connect to a commonly used data source, see one of the following pages:

有关如何连接到此处未列出的数据源的信息,请参阅连接字符串参考For info about how to connect to a data source that's not listed here, see The Connection Strings Reference. 该第三方站点包含示例连接字符串、关于数据提供程序的详细信息以及它们需要的连接信息。This third-party site contains sample connection strings and more info about data providers and the connection info they require.

我需要哪些权限?What permissions do I need?

若要成功运行 SQL ServerSQL Server 导入和导出向导,必须至少具有下列权限。To run the SQL ServerSQL Server Import and Export Wizard successfully, you have to have at least the following permissions. 如果已使用数据源和目标,则可能已具有所需的权限。If you already work with your data source and destination, you probably already have the permissions that you need.

执行这些操作时需要权限You need permissions to do these things 如果要连接到 SQL Server,则需要这些特定权限If you're connecting to SQL Server, you need these specific permissions
连接到源数据库和目标数据库或文件共享。Connect to the source and destination databases or file shares. 服务器和数据库的登录权限。Server and database login rights.
从源数据库或文件中导出或读取数据。Export or read data from the source database or file. 对源表和源视图具有 SELECT 权限。SELECT permissions on the source tables and views.
向目标数据库或文件导入或写入数据。Import or write data to the destination database or file. 对目标表具有 INSERT 权限。INSERT permissions on the destination tables.
创建目标数据库或文件(如果适用)。Create the destination database or file, if applicable. CREATE DATABASE 或 CREATE TABLE 权限。CREATE DATABASE or CREATE TABLE permissions.
保存向导创建的 SSIS 包(如果适用)。Save the SSIS package created by the wizard, if applicable. 如果要将包保存到 SQL ServerSQL Server,需要有将包保存到 msdb 数据库的权限。If you want to save the package to SQL ServerSQL Server, permissions sufficient to save the package to the msdb database.

在向导运行期间获得帮助Get help while the wizard is running

提示

从向导的任何页面或对话框中点击 F1 键,可查看当前页的相关文档。Tap the F1 key from any page or dialog box of the wizard to see documentation for the current page.

向导使用 SQL Server Integration Services (SSIS)The wizard uses SQL Server Integration Services (SSIS)

向导使用 SQL Server Integration Services (SSIS) 复制数据。The wizard uses SQL Server Integration Services (SSIS) to copy data. SSIS 是一种用于提取、转换和加载数据 (ETL) 的工具。SSIS is a tool for extracting, transforming, and loading data (ETL). 向导页面会使用某些 SSIS 语言。The pages of the wizard use some of the language of SSIS.

在 SSIS 中,基本单位是 In SSIS, the basic unit is the package. 当你在向导的各个页面之间移动并指定选项时,向导会在内存中创建 SSIS 包。The wizard creates an SSIS package in memory as you move through the pages of the wizard and specify options.

如果安装了 SQL ServerSQL Server 标准版或更高版本,那么在向导结束时,可以选择保存 SSIS 包。At the end of the wizard, if you have SQL ServerSQL Server Standard Edition or higher installed, you can optionally save the SSIS package. 之后可以重新使用包,或者通过使用 SSISSSIS 设计器添加任务、转换和事件驱动逻辑来扩展包。Later you can reuse the package and extend it by using SSISSSIS Designer to add tasks, transformations, and event-driven logic. SQL ServerSQL Server 导入和导出向导为创建从源向目标复制数据的基本 Integration ServicesIntegration Services 包提供了最简便的方法。The SQL ServerSQL Server Import and Export Wizard is the simplest way to create a basic Integration ServicesIntegration Services package that copies data from a source to a destination.

有关 SSIS 的详细信息,请参阅 SQL Server Integration ServicesFor more info about SSIS, see SQL Server Integration Services.

下一步是什么?What's next?

启动向导。Start the wizard. 有关详细信息,请参阅 启动 SQL Server 导入和导出向导For more info, see Start the SQL Server Import and Export Wizard.

另请参阅See also

导入和导出向导的简单示例入门Get started with this simple example of the Import and Export Wizard
SQL Server 导入和导出向导中的数据类型映射Data Type Mapping in the SQL Server Import and Export Wizard