将数据从一个数据库传输到另一个数据库
我的数据库中有大量数据，需要将这些数据传输到另一台服务器。我正在使用 SerializeJSON 将数据写入文件，这对于少量数据来说效果很好，但现在我遇到了内存不足错误。数据库服务器不在同一网络中。他们无法“看到”彼此，所以这就是我想传输文件中的数据的原因。
关于如何使用文件和 ColdFusion 将数据从一个数据库传输到另一个数据库，有什么想法吗？
I have huge amounts of data in a database and need to transfer that data to another server. I'm using SerializeJSON to write the data to files, that worked fine for smaller amounts of data but now I'm getting out of memory errors. The database servers are not in the same network. They cannor 'see' each other, so that's why I want to transfer the data in files.
Any thoughts about how I can transfer data from one database to another using files and ColdFusion?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我解决这个问题的方法是删除应用程序服务器（ColdFusion）并尝试使用 DTS。这个想法是将每个表中的特定数据库行作为单个导出导出，然后您可以将其导入到另一个数据库中。
因此，请查看您的架构并计算出您需要查询的表，并为每个表编写这些查询。根据新数据库中的主键，您可能需要修改导入语句以避免插入主键，而是让数据库来处理这一点 - 并且您将需要调整引用此（更改的）主键的任何其他导入。
这是一项棘手的工作，我认为您最好使用 DTS 等数据库工具手动完成。
我希望这有帮助。
The way I'd approach this is to cut out the application server (ColdFusion) and try to get the process rolling using DTS. The idea is to export the specific database rows from each table as a single export, which you can then import into the other database.
So look at your schema and work out the tables you need to query, and write those queries for each table. Depending on primary keys in the new database you may need to modify your import statements to avoid inserting the primary key and instead let the database take care of that - and you will need to adjust any other imports referencing this (changed) primary key.
It's a tricky job and I think you are best doing it by hand using database tools such as DTS.
I hope that helps.
您的问题给人的印象是 OOM 正在写数据。也许通过尝试写入整个记录集。相反，您可能需要编写一个迭代数据页的算法 - SQL Server 的 ROW_NUMBER (ORDER BY x, y, z) 是正确的方法。然后，您可以写出多个文件或使用 fileWrite(fileObj, data ) 创建一个大文件 - 后者您可能需要使用 JSON 以外的格式，例如 CSV。
现在，如果问题出在数据插入上，ColdFusion 和 SQL 语句循环存在错误。基本上，当重复循环时，您需要将
INSERT... 最后，如果您在从文件读取数据时遇到问题，则需要执行缓冲读取。在这种情况下，您要么需要使用 CSV 作为序列化格式，要么使用 SAX XML 解析器（如果您使用 XML），但我不知道有以缓冲方式读取的 JSON 解析器。
Your question gives the impression that the OOM is occurring writing out data. Perhaps by attempting to write an entire recordset. Instead you probably need to write an algorithm that iterates through pages of data - SQL Server's ROW_NUMBER(ORDER BY x, y, z) is the right approach for this. You could then write out several files or use fileWrite(fileObj, data) to create one large file - the latter you probably need to do a format other than JSON, such as CSV.
Now if the issue is on data insert, there is a bug with ColdFusion and looping over SQL statements. Basically you need to put the
<cfquery>INSERT...</cfquery>inside<cfthread/>when looping repeatedly.Finally, if you're running into issues reading the data from a file you need to do buffered reads . In this case you'll either need to use CSV as your serialization format or use a SAX XML parser, if you use XML, but I am unaware of a JSON parser that reads in a buffered fashion.
看看 Jailer。它是一个基于 Java 的 GUI 应用程序，允许您选择数据库的特定表并根据某些条件导出数据。它工作得很好而且很容易使用。
Take a look at Jailer. It's a Java based GUI application that allows you to select specific tables of a database and export the data based on some criteria. It works very well and it's easy to use.
如果您有 SQL Server Management Studio 为什么不创建一个完整的备份文件，将所有数据，然后在新数据库上运行还原。这应该恢复所有模式、表和数据。
哈，
拉里
If you have the SQL Server Management Studio why not create a complete backup file, will all the data, then run a restore on the new database. That ought to restore all the schema, tables and data.
hth,
larry