查询迁移实时数据库中的数据？
我正在编写代码以将数据从实时 Access 数据库迁移到新的 Sql Server 数据库，该数据库具有不同的架构和重新组织的结构。 该 Sql Server 数据库将与我们正在开发的应用程序的新版本一起使用。
我一直在用 C# 编写迁移代码，调用 Sql Server 和 Access 并根据需要转换数据。 我第一次迁移一个表，该表包含与我最近未更新的另一个表的新条目相关的条目，这导致了错误，因为找不到 SQL Server 中相应表中的记录
所以，我的 SqlServer 生产表仅包含截至 2009 年 1 月 14 日的数据，我将继续从 Access 迁移更多表。 所以我想写一个更新方法，可以找出Access中哪些新东西没有在Sql Server中体现出来。
我当前的想法是在 SQL 端编写一个查询，执行 SELECT Max(RunDate) FROM ProductionRuns，为我提供表中该字段的最新日期。 在 Access 方面，我会编写一个查询，执行 SELECT * FROM ProductionRuns WHERE RunDate > ？，其中参数是在 SQL Server 中找到的最大日期，并在代码中执行我的转换步骤，然后将新数据插入到 Sql Server 中。
我想知道的是，我是否有正确的语法来获取该 Sql Server 表中的最新日期？ 有没有更好的方法来进行这种实时数据库的迁移？
编辑：我所做的是制作当前实时数据库的副本。 然后我可以迁移而不必担心更改，然后在开发过程中使用它进行测试，然后每当新数据库和应用程序上线时我就可以迁移最新数据。
I am writing code to migrate data from our live Access database to a new Sql Server database which has a different schema with a reorganized structure. This Sql Server database will be used with a new version of our application in development.
I've been writing migrating code in C# that calls Sql Server and Access and transforms the data as required. I migrated for the first time a table which has entries related to new entries of another table that I have not updated recently, and that caused an error because the record in the corresponding table in SQL Server could not be found
So, my SqlServer productions table has data only up to 1/14/09, and I'm continuing to migrate more tables from Access. So I want to write an update method that can figure out what the new stuff is in Access that hasn't been reflected in Sql Server.
My current idea is to write a query on the SQL side which does SELECT Max(RunDate) FROM ProductionRuns, to give me the latest date in that field in the table. On the Access side, I would write a query that does SELECT * FROM ProductionRuns WHERE RunDate > ?, where the parameter is that max date found in SQL Server, and perform my translation step in code, and then insert the new data in Sql Server.
What I'm wondering is, do I have the syntax right for getting the latest date in that Sql Server table? And is there a better way to do this kind of migration of a live database?
Edit: What I've done is make a copy of the current live database. Which I can then migrate without worrying about changes, then use that to test during development, and then I can migrate the latest data whenever the new database and application go live.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我个人会将这个过程分为两个步骤。
这样，您可以编写一组 SQL 代码来完成第二步任务
或者使用 SSIS
I personally would divide the process into two steps.
In that way you can write set of SQL code to accomplish second step task
Alternatively use SSIS
通常，当您将数据转换到将在生产中取代它的新数据库时，您会在一段时间内关闭该数据库的所有用户，运行迁移并打开新数据库。 这可确保在进行转换时不会对数据进行任何更改。 当然，我也永远不会使用 C# 来完成此操作。 数据迁移是一项数据库任务，应该在 SSIS（或 DTS，如果您有旧版本的 SQL Server）中完成。
如果您要转换的数据库刚刚处于开发阶段，我将创建 Access 数据库的备份并从那里加载数据以测试数据加载过程并获取数据，以便您可以进行应用程序开发。 然后，当需要进行真正的加载时，您只需向用户关闭真正的数据库并使用它来加载即可。 如果您试图在开发过程中保持两者同步，那么我不会这样做，但如果您必须这样做，请每晚对文件进行备份，并在早上使用您的进程加载第一件事。
Generally when you convert data to a new database that will take it's place in porduction, you shut out all users of the database for a period of time, run the migration and turn on the new database. This ensures no changes to the data are made while doing the conversion. Of course I never would have done this using c# either. Data migration is a database task and should have been done in SSIS (or DTS if you have an older version of SQL Server).
If the databse you are converting to is just in development, I would create a backup of the Access database and load the data from there to test the data loading process and to get the data in so you can do the application development. Then when it is time to do the real load, you just close down the real database to users and use it to load from. If you are trying to keep both in synch wile you develop, well I wouldn't do that but if you must, make a nightly backup of the file and load first thing in the morning using your process.
您可能想考虑投资 SQL 数据比较 之类的工具。
我相信它也支持access数据库，你可以下载试用版。
You may want to look at investing in a tool like SQL Data Compare.
I believe it has support for access databases too, and you can download a trial.
我对 C# 代码感到满意，但由于目标数据库中的限制，它失败了，您暂时可以 禁用它们，然后在复制全部内容后启用。
我假设您的目标数据库是全新的数据库，没有数据，并且在传输发生时没有被任何人使用
I you are happy with you C# code, but it fails because of the constraints in your destination database you temporarily can disable them and then enable after you copy the whole lot.
I am assuming that your destination database is brand new DB with no data, and not used by anyone when the transfer happens
听起来您有两个问题：
如果您在人们使用数据时尝试迁移数据，那么执行上述任一操作都会很棘手。
最简单的方法是基于数据的静态副本迁移数据，并从捕获静态副本的那一刻起对该数据的更新进行排队。 我不知道这在 Access 中有多容易，但在 SQLServer 或 Oracle 中，您可以使用重做日志或使用触发器的手动解决方案。 穷人的方法是为所有记录已更改记录的主键的相关表创建触发器。 然后，在旧数据库关闭后，您可以迭代这些键并从旧数据库获取这些记录并将它们放入新数据库中。 只需复制整个记录即可； 如果该记录已被删除，则将其从新数据库中删除。
您的问题更加复杂，因为您不能简单地复制数据，您必须对其进行转换。 这意味着您可能必须关闭两个数据库并根据更改列表重新迁移记录。 需要进行大量规划才能确保一切正确，我建议编写一个测试脚本来验证结果数据是否正确。
此外，如果可能的话，我会确保迁移代码在其中一个数据库内运行。 否则，您将复制数据两次，这将严重损害性能。
It sounds like you have two problems:
Doing either of these things is tricky if you are trying to migrate the data while people are using the data.
The simplest approach is to migrate the data based on a static copy of the data, and also to queue updates to that data from the moment you captured the static copy. I don't know how easy this is in Access, but in SQLServer or Oracle you can use the redo logs for this or a manual solution using triggers. The poor-man's way of doing this is to make triggers for all the relevant tables that log the primary key of the records that have changed. Then after the old database is shut off you can iterate over those keys and get those records from the old database and put them into the new database. Just copy the whole record; if the record was deleted then delete it from the new database.
Your problem is compounded by the fact that you can't simply copy the data, you have to transform it. This means you probably have to shut down both databases and re-migrate the records based on the change list. It will take a lot of planning to ensure you get things right and I'd recommend writing a testing script that can validate that the resulting data is correct.
Also I'd ensure that the code for the migration runs inside one of the databases if possible. Otherwise you are copying the data twice and this will significantly harm the performance.