将多个Access DB（功能相同但数据不同）迁移到单个SQL Server DB
情况
我有 5 个 Access DB 文件，每个文件都有 10 个表、40 个查询和 8 个宏。所有 5 个 Access DB 文件具有相同的表名、表结构、相同的查询和相同的宏。唯一的不同是表中包含的数据。如果重要的话，每个数据库上的某些表的行数在几百到 100K+ 之间。
我想要实现的目标
我正在将这 5 个 Access DB 文件迁移到单个 SQL Server (2008) 数据库。 编辑：迁移后，我确实需要知道哪些表属于哪个数据库，因为每个原始 Access DB 都与公司的部门相关联，因此我需要对此进行跟踪。
我的解决方案或选项
表将作为表导入到 SQL Server。查询将作为存储过程导入。宏将作为新的存储过程导入。
- 将每个 Access DB 的表和查询导入到 SQL Server DB，并通过给定前缀来重命名每个表和查询，以识别哪些表属于哪个数据库。
- 但与 #1 相同，仅导入表。至于查询，仅导入一组查询（40 个查询）并修改它们以从表中动态选择、插入、更新或删除。
- 将第一个 Access DB 中的表 A、第二个 Access DB 中的表 A、第三个 Access DB 中的表 A 等导入到 SQL Server 中的一个新表中，并为它们提供唯一标识符以标识哪一行数据属于哪个数据库。
您认为最好的方法是什么？请告诉我是否有比我列出的更好的方法来做到这一点。谢谢！
Situation
I have 5 Access DB files, each one has 10 tables, 40 queries and 8 macros. All 5 Access DB files have same table name, table structure, same queries and same macros. The only different is the data contain in the table. If it matters, some tables on each database has rows between few hundreds to 100K+.
What I am trying to achieve
I am migrating these 5 Access DB files to single SQL Server (2008) database. Edit: After migrating, I do need to know which tables belong to which database since each original Access DB is associated with company's department so I need to keep track of this.
My Solutions or Options
Tables will be imported to SQL Server as tables. Queries will be imported as Stored Procedures. Macro will be imported as new Stored Procedures.
- Import each Access DB's tables and queries to SQL Server DB and rename each tables and queries by giving them prefix to identify which tables belong to which database.
- Same as #1, however, only import tables. As for the queries, only import one set of queries (40 queries) and modify them to dynamically select, insert, update or delete from the tables.
- Import table A from 1st Access DB, table A from 2nd Access DB, table A from 3rd Access DB and so on, to one new table in SQL Server and give them unique identifier to identify which row of data belong to which database.
What do you think is the best approach? Please tell me if there is better way to do this than what I have listed. Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我会将它们迁移到 MS SQL，如下所示：
当这样做时，您可以保持 pk-fk 关系和查询完整，并且仍然知道行来自哪里。
I would migrate them to MS SQL like so:
When doing it this way, you keep the pk-fk relations and the queries intact and still know where the rows came from.
我想说的是第三点。你不会得到重复的代码并且更容易维护。
更容易维护的一个例子是性能调整。您说 5 个访问数据库中的查询是相同的：假设您检测到其中一个查询运行速度太慢，并且您决定需要在基础表上创建索引。在选项 #1 和 #2 中，这意味着在 5 个“孪生”表上重新创建相同的索引。
I would say number 3. You would get no duplication code and much easier maintenance.
One example of easier maintenance is performance tuning. You say the queries are the same in the 5 access DBs: say you detect one of the queries runs too slow and you decide that you need to create an index on an underlying table. In option #1 and #2 this would mean recreating the same index on 5 "twin" tables.
在访问每个数据库时，您可以为每个部门字段 id（新字段）分配新表中的标识符（每个部门有不同的值），然后将此值添加到要访问的每个表中。被进口。创建一个包含部门信息的新表，然后创建连接这些表的联接表。因此，每个部门之间都是有区别的。
In access for each of these databases, you could assign each of the department field id (new field) with it's on identifier in a new table (each department has different value), and then add this value to each of the tables that is to be imported. Create a new table that has the department information in it, then create join table that connect these tables. Thus, each department is differentiated between each other.