将多个Access数据库合并到SQL Server中

Question

我们有一个程序，每个用户都有自己的Access数据库。我们想将它们合并到一个单独的SQL Server数据库中。问题在于，使用SQL Server导入/导出向导时，主键/外键不会得到更新。例如，如果一个用户具有此表格：

1 Apple 2 Banana

另一个用户拥有这个：

1 Coconut 2 Cheeseburger 则结果表格如下所示：

1 Apple 2 Banana 1 Coconut 2 Cheeseburger 同样，任何引用主键为2的Banana的内容现在都同时引用了Banana和Cheeseburger，这对素食主义者来说不是很高兴。

除了编写非常长且复杂的导入脚本之外，是否有自动更新主键/外键引用的方法？

- BlueRaja - Danny Pflughoeft

---

你能否使用用户ID + 当前密钥作为主键？这只需要对表格进行相当小的更改。 - Fionnuala

@Remou：如果可能的话，我想要避免重复项（并且不必编辑每个访问文件中的每个表）。 - BlueRaja - Danny Pflughoeft

@Remou：这仍然需要一个自定义脚本来在导入时分配ID，更不用说需要向成千上万个查询中添加额外的WHERE条件了。 - BlueRaja - Danny Pflughoeft

@Cade：是的，没错，那就是问题的一部分。 - BlueRaja - Danny Pflughoeft

我假设你在简化，因为你的数据没有需要使用代理键的必要。 - David-W-Fenton

Answer 1

如果您需要将它们完全隔离开，您必须为每个表分配某种分区列。您需要让SQL Server具有与Access相同的引用完整性吗？您只是导入SQL Server进行只读报告吗？在这种情况下，我不会费心去处理RI。所有查询都需要一个partitionid/siteid/customerid。您可以通过使用需要partitionid的表值UDF来包装表格来强制执行单实体访问。对于跨站点，这种方法行不通。

如果您只是将其加载到SQL Server进行报告，我还会考虑修改数据模型以支持报告（即维度模型有时比规范化模型更好），而不是担心事务处理。

我认为我们需要了解更多关于基本目标的信息。

- Cade Roux

---

是的，这是一个完整的应用程序 - 一定有比修改每个表、每个查询和编写自定义脚本来加载每个数据库更好的方法。难道没有人以前曾经需要合并两个数据库吗！？ - BlueRaja - Danny Pflughoeft

@BlueRaja - 是的，这绝对不是简单的事情 - 这就是为什么通常多租户架构是最初的要求之一。代码生成肯定可以帮助 - 我在数据库中几乎没有手动编写任何东西 - 大约60％的代码是生成的，大约30％是生成和微调的。如果没有触发器和生成的视图，实际代码的手动部分将会增加很多，但您已经明白了。 - Cade Roux

Answer 2

需要更多的需求信息。

我的基本问题是“您是否需要保留原始记录键？”例如：在用户数据库A的表T中有1:苹果；在用户数据库B的表T中有1:椰子。假设表T在所有数据库实例中具有相同的结构。我可以推测您可能希望保留原始数据的原因有：（a）您可能需要引用原始数据（可能是以前的报告的可视化），和/或者（b）应用程序本身可能存在数据依赖性。

如果答案是“否”，那么您可能只对保留所有不同的数据值感兴趣。允许使用新键构建SQL表，并约束SQL表字段以包含唯一数据。这种方法似乎保留了原始表结构（但不保留原始键值或其“位置”），并且可能足以满足您的要求。

如果答案是“是”，我看不出有其他办法来创建一个保留指向原始数据库和在其表T中创建的键的索引。这种方法似乎需要对应用程序进行修改。

在这种情况下，最好的方法可能是将传入数据分成两个表：一个用于识别数据库和原始密钥，另一个用于识别不同的数据值。例如：（数据库）表D有记录，如"A:1:a"，“A:2:b”，“B:1:c”、“B:2:d”、“B:15:a”、“C:8:a”；（数据）表T1有记录，如"a:apple"，“b:banana”，“c:coconut”，“d:cheeseburger”，其中“A”描述原始数据库“位置”，1是位置“A”中的原始值，“a”是等同于表格D和表格T1中记录的值。（否则，您将在一个表格中有很多冗余数据；例如A：1：apple，B：15：apple，C：8：apple。）此外，T1具有与原始T类似的结构，并且似乎在应用程序中更直接有用。

- TonBill

Answer 3

最终我创建了一个SSIS项目来解决这个问题。SSIS是由微软开发的可视化编程工具，它是他们的“业务集成工作室”（随SQL Server一起提供）的一部分，专门用于解决这些问题。

- BlueRaja - Danny Pflughoeft

Answer 4

为什么不让Access使用它的复制管理器来合并数据库呢？这样可以在导入到SQL Server之前识别冲突并解决它们。我相当有信心它会保留外键关系。如果我正确理解了你的情况，并且这些数据库具有相同的结构但包含不同的数据，你可以将合并后的数据库加载到应用程序中，并在移动到SQL Server之前验证数据。

你使用的是哪个版本的Access？这里是一个针对Access 2000的链接。请使用语言调整搜索参数以适应你的版本。

http://technet.microsoft.com/en-us/library/cc751054.aspx

- Arthur Miller

---

这里是有关Access复制管理器的常见问题解答链接：http://support.microsoft.com/kb/282977 - Arthur Miller

请解释如何使用Jet复制将两个非复制的Jet数据库中的数据合并。 - David-W-Fenton