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

在某些情况下，作为DBA，您需要将模式和特定表的内容从数据库复制到同一实例中或在不同的SQL实例中，例如从生产数据库中复制特定表到开发人员以进行测试或排除故障。

SQL Server提供了许多方法，可以用来执行表的数据和模式复制过程。为了研究这些方法中的每一个，我们将考虑下面的场景:

托管SQL服务器:localhost。这两个数据库都驻留在同一个SQL Server 2014实例中。

源数据库:AdventureWorks2012。

目标数据库:SQLShackDemo。

将从源数据库复制到目标数据库的表有:Department、Employee、EmployeeDepartmentHistory和EmployeePayHistory。

表复制方法:

使用 SELECT INTO 查询语句

为了将我们的表从AdventureWorks2012数据库复制到SQLShackDemo数据库，我们可以使用SELECT INTO SQL语句。该语句将首先在目标数据库中创建表，然后将数据复制到这些表中。如果您设法复制数据库对象，如索引和约束，您需要为它单独生成脚本，然后您需要将脚本应用到目标数据库。

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

Select*intoSQLShackDemo.HumanResources.DepartmentfromAdventureWorks2012.HumanResources.Department

Select*intoSQLShackDemo.HumanResources.EmployeefromAdventureWorks2012.HumanResources.Employee

Select*intoSQLShackDemo.HumanResources.EmployeeDepartmentHistoryfromAdventureWorks2012.HumanResources.EmployeeDepartmentHistory

Select*intoSQLShackDemo.HumanResources.EmployeePayHistoryfromAdventureWorks2012.HumanResources.EmployeePayHistory

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

如果任何表包含标识列，目标表中的新列将继承标识属性，而不需要打开IDENTITY_INSERT。

这在大多数情况下都是有效的，除非SELECT语句包含JOIN，否则使用UNION来连接多个SELECT语句，在SELECT语句中多次提到IDENTITY列，或者该标识列的源是远程数据源。

如果上述条件中的任何一个为真，则将使用非空属性创建列，而不是继承所需的标识属性。

为了克服这个身份问题，您可以使用select语句中的IDENTITY SQL函数来创建标识列。

使用SQL Server导出/导入向导

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

在SQL Server Management Studio中，右键单击object explorer中的AdventureWorks2012数据库，然后选择Tasks ->导出数据:

在“选择导入和导出向导”的数据源窗口中，指定源服务器名称、用于连接源服务器的身份验证方法以及源数据库名称，然后单击Next。

在SQL导入和导出向导的选择目标窗口中，指定目标服务器名称、用于连接目标服务器的身份验证方法和目标数据库名称，然后单击Next。

在SQL导入和导出向导的指定表复制或查询中，从一个或多个表或视图中选择复制数据。单击Next。

在SQL导入和导出向导的Select源表和视图中，选择将从所选源数据库复制到目标数据库的表，然后单击Next

为了确保将在目标数据库中创建表，请单击Edit Mappings，确保创建目标表被选中，如果您的任何表包含标识列，请确保选中Enable IDENTITY Insert，然后单击OK。如果要将多个表导出到目标数据库，则需要逐个查看编辑映射检查表。

在“选择源表和视图”中单击“下一步”。

在Run包中，单击Next。

在完成向导时，单击Finish。

成功完成执行之后，您可以查看迁移表所遵循的步骤和传输的记录数量。检查步骤和消息，如果没有错误，请单击Close。

如果您安排将表复制到目标数据库，而不关心表的关系和顺序，那么此方法是将表从源数据库复制到目标数据库的一种快速方法。

使用此方法，表的索引和键将不会被转移。如果您有兴趣复制它，您需要为这些数据库对象生成脚本。

如果这些是连接这些表的外键，则需要以正确的顺序导出数据，否则导出向导将失败。

Generate Scripts

SQL Server提供了另一种为SQL Server数据库及其对象和数据生成脚本的方法。此脚本可用于将表的模式和数据从源数据库复制到目标数据库。

使用SQL Server Management Studio，从对象资源管理器右键单击源数据库，然后从任务选择生成脚本。

在“选择对象”窗口中，选择“选择特定的数据库对象”，以指定要为其生成脚本的表，然后通过在表的每个表旁边勾选这些表。单击Next。

在Set脚本选项窗口中，指定你要保存生成脚本文件的路径，然后单击Advanced。

在“出现的高级脚本选项”窗口中，将模式和数据指定为脚本的数据类型。您可以从这里决定是否要在表中编写索引和键。单击OK。

回到高级脚本选项窗口，单击Next。

查看Summary窗口并单击Next。

您可以从Save或Publish Scripts窗口监视进度。如果没有错误，单击Finish，您将在指定的路径中找到脚本文件。

SQL脚本方法对于为表的模式和数据(包括索引和键)生成一个脚本非常有用。但是，如果表之间有关系，这个方法不会以正确的顺序生成表的创建脚本。

使用ApexSQL Diff和ApexSQL数据Diff组合

ApexSQL Diff是一个有用的SQL工具，它可以用来发现数据库与模式之间的差异，并生成同步脚本以正确的顺序在目标数据库中创建这些表。

ApexSQL Data Diff也是一种SQL工具，可以使用它从数据端查找数据库之间的差异，并生成同步脚本，在目标数据库表中插入数据，同时考虑到IDENITY列。

我们可以利用这两个工具的优点来生成所需的脚本，以创建与其对象一起复制的表，并将数据从源表复制到目标表。

启动ApexSQL Diff工具。

在数据源面板中，指定源服务器名、源数据库名和用于连接源服务器的身份验证方法。如果选择SQL Server身份验证，则需要指定有效的使用名称和密码。

还指定目标服务器名称、目标数据库名称和用于连接目标服务器的身份验证方法。如果选择SQL Server身份验证，则需要指定有效的使用名称和密码。

您可以通过单击Reverse来更改指定为源或目标服务器的每个服务器的角色。然后单击连接。

点击进行比较。

将显示一个新窗口，其中包含两个数据库之间常见的对象，这些对象存在于其中一个数据库中，但在第二个数据库中不存在。

在我们的示例中，我们对存在于AdventureWorks2012数据库中而不存在于SQLShackDemo数据库中的对象感兴趣，在这个数据库中，我们可以找到要复制的表。

展开仅存在于AdventureWorks2012数据库中的对象，并选择要复制的表。点击同步。

在同步向导窗口的同步方向中，如果不想更改同步方向，请单击Next。

在“同步向导”窗口的依赖项中，如果不想编写相关表的脚本，请取消对包含相关数据库对象的检查。单击Next。

在“同步向导”窗口的输出选项中，如果您想要生成脚本并手动运行脚本，请选择创建一个同步脚本，或者现在就进行同步，以便工具可以直接在目标服务器上运行脚本。单击Next。

在“同步向导”窗口的摘要和警告中，检查操作并单击“创建脚本”。

现在生成了使用对象创建数据库表的脚本，并按正确的顺序生成。您可以在目标数据库上运行此脚本来创建表。

启动ApexSQL数据Diff工具。

在数据源面板中，指定源服务器名、源数据库名和用于连接源服务器的身份验证方法。如果选择SQL Server身份验证，则需要指定有效的使用名称和密码。

还指定目标服务器名称、目标数据库名称和用于连接目标服务器的身份验证方法。如果选择SQL Server身份验证，则需要指定有效的使用名称和密码。

您可以通过单击Reverse来更改指定为源或目标服务器的每个服务器的角色。然后单击连接。

点击进行比较。

将显示一个新窗口，该窗口包含源数据库表和目标数据库之间的数据差异，以及相同和不可比较的数据。

这里我们感兴趣的是源数据库和目标数据库中的表之间的差异。与前面的步骤一样，我们创建了这些表，但它仍然是空的。

从差异结果网格中，选择需要将数据复制到目标数据库的表，然后单击Synchronize。

在同步向导窗口的同步方向中，如果不想更改同步方向，请单击Next。

在“同步向导”窗口的输出选项中，如果您想要生成脚本并手动运行脚本，请选择创建一个同步脚本，或者现在就进行同步，以便工具在目标服务器上运行脚本。单击Next。

在“同步向导”窗口的摘要和警告中，检查操作并单击“创建脚本”。

现在，将生成用于插入数据库表(处理标识插入)的脚本。您可以在目标数据库上运行此脚本，以插入表的数据。

ApexSQL Diff和ApexSQL数据Diff工具的组合对于复制SQL数据库表很有用，方法是为这些表的模式生成一个完整的脚本，其中包含索引和键，并且顺序正确，表的数据处理标识列插入。

使用ApexSQL脚本:

ApexSQL提供的另一个有用工具是ApexSQL脚本工具，它可用于将SQL Server表数据和模式从源数据库复制到目标数据库。这个不错的工具将使用处理标识列插入的这些表的索引和键为数据库表模式和数据创建脚本。

启动ApexSQL脚本工具。

在Select database面板中，指定源服务器名和用于连接源服务器的身份验证方法。如果选择SQL Server身份验证，则需要指定有效的使用名称和密码。单击连接。

服务器中托管的所有数据库的列表将在正确的结果网格中查看。在我们的示例中选择AdventureWorks2012的源数据库名称。

在数据面板中，从通用部分中选择Set IDENTITY_INSERT，并从脚本行中插入section。单击Open。

从显示的窗口中，选择要复制到目标数据库的表，并单击Script:

从脚本向导中选择结构和数据作为脚本模式和T-SQL作为输出类型。单击Next。

在Dependencies窗口中，单击Next。

在“输出文件”窗口中，从“操作”下拉列表中选择“创建”和“写入文件”。然后您可以选择将脚本保存到特定的路径中，或者在编辑器中打开它。选择编辑器中的打开脚本，然后单击Create。

现在生成一个完整的脚本。只要在目标数据库上运行它，表就会被完全复制。您可以通过脚本以正确的顺序检查表的创建脚本，如下所示:

还可以找到表数据的INSERT语句，如下所示:

ApexSQL脚本是一个非常有用的工具，可以使用它将SQL Server数据库表从源数据库复制到目标数据库，而无需您自己处理表的创建顺序。也不需要从您这边来处理身份插入。

结论:

如您所见，可以使用多个方法将表从源数据库复制到目标数据库，包括模式和数据。这些工具中的大多数都需要您付出很大的努力来复制表的对象，比如索引和键。而且，如果表之间存在关系，这些工具也不会处理这些表的创建顺序。从数据方面来看，这些工具中的大多数需要从您的一侧额外的步骤来处理身份插入。使用ApexSQL Diff和ApexSQL数据Diff的组合，或者使用ApexSQL脚本，通过一些简单的步骤，您将拥有一个完整的数据库表模式和数据脚本，而无需您额外的努力来处理任何事情。只需在目标数据库上运行这些脚本，并享受结果。