将大量数据从Oracle复制到SQL Server的最快方法 [英] Fastest way to copy large amounts of data from Oracle to SQL Server
问题描述
我需要将大量数据从Oracle数据库复制到SQL Server数据库.最快的方法是什么?
I need to copy large amounts of data from an Oracle database to a SQL Server database. What is the fastest way to do this?
我正在查看占用Oracle 60-70 GB存储空间的数据.我使用的方法没有特别限制.我可以使用SQL Server Management Studio，SQL Serer导入/导出程序，.NET应用程序或Oracle中的开发人员界面，第三方工具或----.我只需要尽快移动数据.
I am looking at data that takes 60 - 70 gig of storage in Oracle. There are no particular restrictions on the method that I use. I can use the SQL Server Management Studio, or the SQL Serer import/export program, or a .NET app, or the developer interface in Oracle, or third party tools, or ----. I just need to move the data as quickly as possible.
数据按地理位置进行组织.每个状态的数据都会分别更新到Oracle数据库中，并且可以单独移至SQL Server.因此，很少会一次移动全部数据.
The data is geographically organized. The data for each state comes is updated separately into the Oracle database and can be moved over to SQL Server on its own. So the entire volume of the data will rarely be all moved over at once.
那么人们会有什么建议?
So what suggestions would people have?
推荐答案
将大量数据插入SQL Server的最快方法是使用SQL Server批量插入.常见的批量插入技术为:
The fastest way to insert large amounts of data into SQL Server is with SQL Server bulk insert. Common bulk insert techniques are:
- T-SQL BULK INSERT语句
- BCP命令行实用程序
- 带有快速加载选项的SSIS包OLE DB目标
- 来自非托管代码的ODBC bcp API
- 来自非托管代码的OLE DB IRowsetFastLoad .li应用程序中的
- SqlBulkCopy
- T-SQL BULK INSERT statement
- BCP command-line utility
- SSIS package OLE DB destination with the fast load option
- ODBC bcp API from unmanaged code
- OLE DB IRowsetFastLoad from unmanaged code
- SqlBulkCopy from a .NET application
T-SQL BULK INSERT和命令行BCP实用程序使用平面文件源，因此，这就意味着您需要首先将数据导出到文件中.其他方法可以直接使用Oracle SELECT查询结果，而无需中间文件，只要不考虑源/目标网络的带宽和延迟，该文件的整体效果就会更好.
T-SQL BULK INSERT and the command-line BCP utility use a flat file source so the implication is that you'll need to first export data to files. The other methods can use Oracle SELECT query results directly without the need for an intermediate file, which should perform better overall as long as source/destination network bandwidth and latency isn't a concern.
使用SSIS，通常会为要复制的每个表创建一个数据流任务，并使用OLE DB源(Oracle)和OLE DB目标(SQL Server).可以根据SSIS版本单独下载Oracle源提供程序.最新的是适用于Oracle的Microsoft连接器v4.0 . SSMS导入向导可用于生成任务的SSIS程序包，该程序包可立即运行和/或根据需要保存和自定义.例如，您可以为要复制的状态创建一个包变量，并在刷新数据之前在源SELECT查询和目标DELETE查询中使用该变量.这样一来，相同的程序包就可以在任何状态下重复使用.
With SSIS, one would typically create a data flow task for each table to be copied with a OLE DB source (Oracle) and OLE DB destination (SQL Server). The Oracle source provider can be downloaded separately depending on the SSIS version. The latest is the Microsoft Connector v4.0 for Oracle. The SSMS import wizard can be used to generate an SSIS package for the task, which may be run immediately and/or saved and customized as desired. For example, you could create a package variable for the state to be copied and use that in the source SELECT query and in a target DELETE query prior to refreshing data. That would allow the same package to be reused for any state.
OLE DB IRowSetFastLoad或ODBC bcp调用的性能应与SSIS相似，但是您可以在关注细节的情况下获得一些额外的性能提升.但是，除非您已经熟悉C ++和API，否则使用这些API并非易事.
OLE DB IRowSetFastLoad or ODBC bcp calls should perform similarly to SSIS but you might be able to eek out some additional performance gains with a lot of attention to detail. However, using these APIs is not trivial unless you are already familiar with C++ and the APIs.
SqlBulkCopy速度很快(通常每分钟数百万行)，对于大多数应用程序而言，其性能已经足够好，而没有非托管代码的额外复杂性.最好将 Oracle托管提供程序用于源SELECT查询，而不是.NET代码中的ODBC或OLE DB提供程序.
SqlBulkCopy is fast (generally millions of rows per minute), which is good enough performance for most applications without the additional complexity of unmanaged code. It will be best to use the Oracle managed provider for the source SELECT query rather than ODBC or OLE DB provider in .NET code.
我的建议是，您不仅要考虑性能，还要考虑现有技能.
My recommendation is you consider not only performance but also your existing skillset.
这篇关于将大量数据从Oracle复制到SQL Server的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
