使用 VB.NET 将数据从一个数据库复制到另一个数据库
我需要使用 VB.NET 程序将数据从一个数据库复制到另一个数据库。 目标数据库是 SQL Server，源数据库是一些专有的 ODBC 兼容数据库。
我需要循环遍历要复制的表列表。从源数据库表中读取给定修改日期的数据。从目标数据库表中删除相应日期并从源表中插入记录。数据库具有相同的结构，即表名和字段名，但数据类型可能不同（但是它们是兼容的，例如源中为双精度，目标中为浮点）。不存在主键。
我可以这样做：
首先对目标执行删除命令。 然后，我可以使用 DataReader 从源获取数据，循环遍历项目并为每行创建一个插入命令。使用适当的值将参数添加到命令并执行。并将整个事情包装在一个事务中。
我只是想知道我是否在这里错过了一个技巧。任何建议
I need to copy data from one database to another using a VB.NET program.
The target database is SQL Server the source database is some proprietary ODBC compliant database.
I need to loop through a list of table to copy. Read the data from the source database table for a given modified date. Delete the corresponding date from the target database table and insert the records from the source table. The databases are of the same structure i.e. table names and field names, but the data types may differ (however they are compliant e.g. double in source, float in target). No primary keys exist.
Heres how I may do it :
Firstly execute a Delete command to the target.
I could then use a DataReader to obtain data from the source, loop through the Items and create an Insert Command for each row. Add Parameters to the Command with the appropriate values and execute. And wrap the whole thing in a Transaction.
I was just wondering if I am missing a trick here. Any Suggestions
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我认为你应该使用适合这项工作的权利，我猜在这种情况下就是 SSIS，但我可能是错的，也许你已经探索了这条道路。
在这种情况下，是的，数据读取器将根据您拥有的数据量进行操作。数据表甚至可能更容易、更快速地编程（无需担心数据类型，因为适配器应该处理这一点。
I think you should use the right for the job and I'm guessing that that is SSIS in this case, but I could be wrong and perhaps you have already explored that path.
In that case yes a datareader would do depnding how much data you have. A datatable might even be eassier and faster to program (no need to worry about datatypes since the adapter should take care of that.
诀窍是使用基于集合的操作，而不是我们程序员首先被教导的“一次行”概念:)
这是一些伪代码
也许您的要求更复杂，可能需要逐行方法，但这通常不是案件。
我选择将此代码放在 SQL 上的作业步骤和计划中。它也可能是从 .net 运行的存储过程。
此外，使用 SSIS 进行数据库到数据库的传输很可能是多余的，除非您要在其中使用一些特殊的转换。
The trick would be to use set based operations and not the 'row at a time' concept which we programmers were first taught :)
Here's some pseudocode
Perhaps your requirements are more complicated and may need the row by row approach, but this is normally not the case.
I'd opt to put this code in a job step and schedule on SQL. It could also be a stored procedure run from .net.
Also, using SSIS for a db to db transfer is most likely overkill unless you are going to be using some of the special transformations in there.
查看 SqlBulkCopy 类。如果您可以将源放入 DataTable 或使用 IDataReader 读取它，那么它就符合条件。它还将尝试在兼容类型之间进行转换。有关详细信息，请参阅单个批量复制操作。
这比对每行使用 INSERT 语句更理想。
Take a look at the SqlBulkCopy class. If you can get the source into a DataTable or read it with an IDataReader then it's eligible. It will also attempt to convert between compatible types. See Single Bulk Copy Operations for more details.
This would be more desirable than using INSERT statements for each row.
首先检查所有表
Check all the tables first