如何在SQL Server中将表从一个数据库复制到另一个数据库

In some cases, as a DBA, you are requested to copy the schema and the content of specific tables from a database to another one in the same instance or in a different SQL instance, such as copying specific tables from a production database to a DEV one for testing or troubleshooting purposes.

在某些情况下，作为DBA，要求您将模式和特定表的内容从数据库复制到同一实例或不同SQL实例中的另一数据库，例如将特定表从生产数据库复制到DEV。一种用于测试或故障排除的目的。

SQL Server offers a lot of methods that can be used to perform table’s data and schema copy process. In order to go through each one of these methods, we will consider the below scenario:

SQL Server提供了许多可用于执行表的数据和架构复制过程的方法。 为了遍历这些方法中的每一种，我们将考虑以下情形：

• The hosting SQL Server: localhost.
  托管SQL Server：本地主机。
• Both databases hosted in the same SQL Server 2017 instance
  两个数据库托管在同一SQL Server 2017实例中
• AdventureWorks2018 AdventureWorks2018
• SQLShackDemo SQLShackDemo

The tables that will be copied from the source database to the destination one are: Department, Employee, EmployeeDepartmentHistory and EmployeePayHistory under the HumanResources schema.

将要从源数据库复制到目标数据库的表是在HumanResources模式下的Department ， Employee ， EmployeeDepartmentHistory和EmployeePayHistory 。

Tables Copying Methods:

表格复制方法：

Using SELECT INTO Query

使用SELECT INTO查询

In order to copy our tables from the AdventureWorks2018 database to the SQLShackDemo one, we can use the Select into SQL statement. This statement will create the tables in the destination database first, then it will copy the data to these tables. If you manage to copy the database objects such as the indexes and constraints, you need to generate script for it individually, after that you need to apply the scripts to the destination database.

为了将表从AdventureWorks2018数据库复制到SQLShackDemo之一，我们可以使用Select into SQL语句。 该语句将首先在目标数据库中创建表，然后将数据复制到这些表中。 如果您设法复制数据库对象（例如索引和约束），则需要为其单独生成脚本，然后再将脚本应用于目标数据库。

In our example, to copy the Department, Employee, EmployeeDepartmentHistory and EmployeePayHistory tables under the HumanResources schema from the AdventureWorks2018 database to the SQLShackDemo database, we will run the below script:

在我们的示例中，要将HumanResources模式下的Department ， Employee ， EmployeeDepartmentHistory和EmployeePayHistory表从AdventureWorks2018数据库复制到SQLShackDemo数据库，我们将运行以下脚本：

Create schema HumanResources
Go
 
Select * into SQLShackDemo.HumanResources.Department from 
AdventureWorks2018.HumanResources.Department 
Select * into SQLShackDemo.HumanResources.Employee from 
AdventureWorks2018.HumanResources.Employee 
Select * into SQLShackDemo.HumanResources.EmployeeDepartmentHistory from 
AdventureWorks2018.HumanResources.EmployeeDepartmentHistory 
Select * into SQLShackDemo.HumanResources.EmployeePayHistory from 
AdventureWorks2018.HumanResources.EmployeePayHistory

The columns in destination tables are created in the order specified in the select statement. All these columns have the exact name, data type, nullability property, and column value as in the source table.

目标表中的列是按select语句中指定的顺序创建的。 所有这些列都具有与源表中相同的确切名称，数据类型，可空性属性和列值。

If any of the tables contains an Identity column, the new column in the destination table will inherit the Identity property without the need to turn on the Identity_Insert.

如果任何表包含Identity列，则目标表中的新列将继承Identity属性，而无需打开Identity_Insert。

This is valid in most cases unless the Select statement contains Join, you are using Union to join multiple Select statements, the Identity column is mentioned many times in your select statement or the source of this Identity column is a remote data source.

在大多数情况下，这是有效的，除非Select语句包含Join，您正在使用Union来联接多个Select语句，在select语句中多次提到了Identity列，或者此Identity列的源是远程数据源。

If any one of these conditions mentioned is true, the column will be created with Not null property instead of inheriting the required Identity property.

如果上述任一条件为true，则将使用Not null属性创建该列，而不是继承所需的Identity属性。

To overcome this Identity issue, you can use the Identity SQL function in the select statement to create the Identity column.

要解决此Identity问题，可以在select语句中使用Identity SQL函数来创建Identity列。

Using SQL Server Export/Import wizard

使用SQL Server导出/导入向导

Another method that can be used to copy tables from the source database to the destination one is the SQL Server Export and Import wizard, which is available in SQL Server Management Studio. You have the choice to export from the source database or import from the destination one in order to transfer the data:

可用于将表从源数据库复制到目标数据库的另一种方法是SQL Server导出和导入向导，该向导可在SQL Server Management Studio中使用。 您可以选择从源数据库导出或从目标数据库导入，以便传输数据：

1. AdventureWorks2018 database in the Object Explorer中的Object Explorer, then from AdventureWorks2018数据库，然后从Tasks choose the Tasks中选择Export Data command: Export Data命令：
   
   
   
   
   

2. In the Choose a Data Source step of the SQL Server Import and Export Wizard, specify the source Server name, the Authentication method that will be used to connect to the source server, and the source Database name, then click the Next button:
   
   
   
3. 在“ SQL Server 导入和导出向导”的“选择数据源”步骤中，指定源服务器名称，将用于连接到源服务器的身份验证方法以及源数据库名称，然后单击“ 下一步”按钮：
   
   

4. In the Choose a Destination step of the SQL Server Import and Export Wizard, specify the destination Server name, the Authentication method that will be used to connect to the destination server and the destination Database name, then click the Next button:
   
   
   
5. 在“ SQL Server导入和导出向导”的“选择目标”步骤中，指定目标服务器名称，将用于连接到目标服务器的身份验证方法和目标数据库名称，然后单击“ 下一步”按钮：
   
   

6. Specify Table Copy or Query step of the SQL Server导入和导出向导”的“ SQL Server Import and Export Wizard, choose the 指定表复制或查询”步骤中，选择“ Copy data from one or more tables or views option and click the 从一个或多个表或视图复制数据”选项，然后单击“ Next button: 下一步”按钮：
   
   
   
   
   
   
   
   
7. Select Source Tables and Views step of the SQL Server导入和导出向导”的“ SQL Server Import and Export Wizard, choose the tables that will be copied from the chosen source database to the destination one: 选择源表和视图”步骤中，选择将要从所选源数据库复制到目标数据库的表：