如何将数据从 SQL Server 拉到 Oracle？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4795643/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you pull data from SQL Server to Oracle?
提问by Oscar
I'm wanting to take data from a SQL Server table and populate a Oracle table. Right now, my solution is to dump the data into a Excel table, write a macro to create a sql file that I can load into Oracle. The problem with this is I want to automate this process and I'm not sure I can automate this.
我想从 SQL Server 表中获取数据并填充 Oracle 表。现在，我的解决方案是将数据转储到 Excel 表中，编写一个宏来创建一个可以加载到 Oracle 中的 sql 文件。问题是我想自动化这个过程，但我不确定我可以自动化这个过程。
Is there an easy way to automate populating a Oracle table with data from a SQL Server table?
是否有一种简单的方法可以使用 SQL Server 表中的数据自动填充 Oracle 表？
Thanks in advance
提前致谢
回答by Paul Sasik
Yes. Take a look at MS SQL's SSIS which stands for SQL Server Integration Services. SSIS allows all sorts of advanced capabilities, including automated with Sql Server Jobs, for moving data between disparate data sources. In your case, connecting to Oraclecan be achieved a variety of ways.
是的。看看 MS SQL 的 SSIS，它代表SQL Server Integration Services。SSIS 允许各种高级功能，包括自动使用 Sql Server 作业，用于在不同的数据源之间移动数据。在您的情况下，可以通过多种方式连接到 Oracle。
回答by Justin Cave
I suppose it depends on your definition of "easy".
我想这取决于你对“简单”的定义。
The most robust approach would be to either use heterogeneous connectivityin Oracle to create a database link to the SQL Server database and then pull the data from SQL Server or to create a linked serverin SQL Server that connects to Oracle and then push the data from SQL Server to Oracle.
最可靠的方法是在 Oracle 中使用异构连接创建到 SQL Server 数据库的数据库链接，然后从 SQL Server 中提取数据，或者在 SQL Server 中创建一个链接到 Oracle的链接服务器，然后从SQL Server 到 Oracle。
回答by Solomon Rutzky
There are three ways to automate this:
有以下三种方法可以自动执行此操作：
1) You can do as Paul suggested and created an SSIS package that will do this and it can be scheduled via SQL Agent,
1) 您可以按照 Paul 的建议进行操作，并创建一个 SSIS 包来执行此操作，并且可以通过 SQL 代理进行调度，
2) If you don't want to deal with SSIS, you can download the free SQL# (SQLsharp) CLR Library from http://www.SQLsharp.com/and use the DB_BulkCopy Stored Procedure to do this in a T-SQL Stored Proc which can also be scheduled via SQL Agent. [note: I am the author of SQL#]
2) 如果您不想处理 SSIS，您可以从http://www.SQLsharp.com/下载免费的 SQL# (SQLsharp) CLR 库并使用 DB_BulkCopy 存储过程在 T-SQL 中执行此操作存储过程，也可以通过 SQL 代理进行调度。[注：我是SQL#的作者]
3) You can also set up a Linked Server from SQL Server to Oracle, but this has the draw-back of being a potential security hole. Of course, you could use an Oracle Login that only has write-access to that single table (or something similar to that).
3) 您也可以设置从 SQL Server 到 Oracle 的链接服务器，但这有一个潜在的安全漏洞的缺点。当然，您可以使用仅对该单个表（或类似的）具有写访问权限的 Oracle 登录名。
回答by Cheeso
There are lots and lots of ways to do it. Which you choose depends on your requirements.
有很多方法可以做到。您选择哪个取决于您的要求。
- Using Excel is fine if it's a one time thing.
- If it's a once-in-a-while thing, then you could write a simple .NET app that uses a single DataSet and multiple DataAdapters to do the data dump. C# code example here.
- if it's a regular thing, then you could put the above in a Schtasks task, or you could use SSIS. I think SSIS is an extra-cost option.
- if the requirement is for "online access", then a linked database is probably appropriate.
- 如果它是一次性的，使用 Excel 很好。
- 如果这是偶尔发生的事情，那么您可以编写一个简单的 .NET 应用程序，该应用程序使用单个 DataSet 和多个 DataAdapter 来执行数据转储。 C# 代码示例在这里。
- 如果这是常规的事情，那么您可以将上述内容放在 Schtasks 任务中，或者您可以使用 SSIS。我认为 SSIS 是一种额外收费的选择。
- 如果要求是“在线访问”，那么链接数据库可能是合适的。
