如何在保持外键关系/引用完整性的同时在多个数据库表之间移动数据？ [英] How to move data between multiple database's table while maintaining foreign-key relationships/referential integrity?

问题描述

我想弄清楚将多个数据库中的几个数据表移动/合并成一个数据库的最佳方法。

I'm trying to figure out the best way to move/merge a couple tables of worth of data from multiple databases into one.

我有一个类似以下的模式：

I have a schema similar to the following:

CREATE TABLE Products(
    ProductID int IDENTITY(1,1) NOT NULL,
    Name varchar(250) NOT NULL,
    Description varchar(1000) NOT NULL,
    ImageID int NULL
)

CREATE TABLE Images (
    ImageID int IDENTITY(1,1) NOT NULL,
    ImageData image NOT NULL
)

使用产品图片ID的外键到图片的ImageID。

With a foreign-key of the Products' ImageID to the Images' ImageID.

因此，将这些表中包含的数据从多个源数据库移动到具有相同模式的一个目标数据库的最佳方法是什么。

So what's the best way to move the data contained within these table from multiple source databases into one destination database with the same schema. My primary issue is maintaining the links between the products and their respective images.

推荐答案

在SQL Server中，您可以启用身份插入：

In SQL Server, you can enable identity inserts:

SET IDENTITY_INSERT NewTable ON
<insert queries here>
SET IDENTITY_INSERT NewTable OFF

启用身份插入功能后，标识列与任何其他列相同。这允许您只需复制表，例如从链接服务器：

While idenitity insert is enabled, you can insert a value in the identity column like any other column. This allows you to just copy the tables, for example from a linked server:

insert into newdb.dbo.NewTable
select *
from oldserver.olddb.dbo.OldTable

这篇关于如何在保持外键关系/引用完整性的同时在多个数据库表之间移动数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！