如何自动将来自 Oracle 的数据存储在 SQL Server 中（根据计划）
你好， 我是新来的，很抱歉，如果我的问题太基本了。 然而，也许你有一些建议，例如，链接，可以帮助我......我试图找到一些有用的东西几天，但目前没有结果。
我在分布式环境中工作。 我在数百英里外有一台 Oracle 服务器，在我附近有一台 MS SQL 服务器。 我正在使用 Visual Web Developer 2008 Express 编写一个应用程序。 我需要来自 Oracle 的一些数据。 每次我需要 Oracle 服务器中的一些数据时，都不值得去查询它。 我更喜欢每晚运行一次 Oracle 查询并将结果存储在一些本地 (SQL Server) 表中。 我认为，我应该通过标准 Windows 调度程序 (Windows Server 2008) 运行查询。 我有基本的连接 - 我可以从本地 Visual Studio 打开 Oracle 数据库。
问题是：
- 如何编写一个查询/过程/函数来从 Oracle 获取数据并将它们放入 SQL Server 表中（可能在每次查询运行之前重新创建）？
- 如何从命令行运行这样的查询（或以其他方式从调度程序运行）
- 适用哪些命名约定？ 在 VS 中，我使用类似 //IP.IP.IP.IP/Name 和带有密码的用户。
感谢您的任何帮助或建议。
问候， 马泰奥
Hello,
I'm new here, so sorry, if my question is too basic. However, maybe you have some advice, example, links, which could help me... I'm trying to find something helpfull for few days, but no results as for now.
I'm working in a distributed environment. I have a Oracle server hundreds of miles away and a MS SQL server close to me. I'm writing a application using Visual Web Developer 2008 Express. I need some data from Oracle. It's not worth to query the Oracle server every time i need some data from it. I'd prefer to run some Oracle queries once each night and store results in some local (SQL Server) tables. I assume, I should run queries through standard windows scheduler (Windows Server 2008). I have the basic connectivity - I can open Oracle Database from local Visual Studio.
The questions are:
- How to write a query/procedure/function that would get data from Oracle and put them into a SQL Server table (possibly recreated before each query run)?
- How can I run such a query from command line (or in other way run from scheduler)
- What naming conventions are applicable? In VS I use something like //IP.IP.IP.IP/Name and a user with password.
Thanks for any help or advice.
Regards,
Matteo
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我建议您咨询 Oracle 和 SQL Server 数据库的 DBA，因为您可能还需要牢记其他注意事项。 （数据完整性、安全性、所有权等）
您可以遵循的一种途径是实施 DTS（对于旧数据库）或 SSIS（对于新版本的 SQL Server）进程，以按照您想要的时间表复制数据。 （这几乎就是他们建造的目的。）
I suggest you speak to the DBA's of the Oracle and SQL Server databases, as there may be other considerations you need to bear in mind. (Data Integrity, Security, ownership etc.)
One route you could follow would be to implement DTS (For older databases) or SSIS (for new versions of SQL Server) processes to copy the data across on the schedule you want. (This is pretty much what they were built for.)
我们谈论的是多少数据？
如果您每天需要传输的数量很少，您可以用您选择的语言编写一个愚蠢的获取和插入脚本。
如果“同步”会占用太多资源，您只需寻找更好的解决方案。
How much data are we talking about?
If there is a small quantity that you need to transfer every day, you can write a stupid fetch and insert script in language of your choice.
You only need to search for better solutions if "sync" would take too much resources.
谢谢...
我是 SQL Server 的 DBA，它只为我的应用程序服务。 对于Oracle，我只想读取数据，并且我有足够的权限并与DBA 达成一致。 安全性、所有权和完整性目前不是问题。 我只需要一些技术建议，如何按计划将数据从 Oracle 获取到 MSSQL 表。
我使用 MS SQL Server 2008 Express SP1。 我非常接近解决我的问题 - 我已经建立了连接并且所有东西都已安装并正常工作。 我只是不知道如何运行查询，该查询会定期从 Oracle 获取数据并放入 MSSQL，而无需手动交互。
我有一些编程经验，但在数据库方面没有太多经验（除了创建复杂的 SQl 查询）。 因此，一些示例或详细描述的链接会很有帮助。 我不确定命名约定、过程、函数和查询之间的差异、运行数据库自动化过程的命令行选项等等。 我也不确定 MS SQL Server 2008 Express 版本中提供哪些机制或技术。
Thanks...
I'm the DBA for the SQL Server, which will serve only for my application. For Oracle I just want to read data and I have enough privileges and agreement with DBA's. Security, ownership and integrity are not an issue for now. I just need some technical advise how to get data from Oracle to MSSQL tables on a schedule.
I use MS SQL Server 2008 Express SP1. I'm very close to solve my problem - I have established connections and everything installed and working. I just don't know, how to run a query, which would get data from Oracle and put into MSSQL, on regular basis, without manual interaction.
I've some experience in programming, but not much in databases (except creating complex SQl queries). Therefore some example or links to detailed description would be helpful. I'm not sure about naming conventions, differences between procedures, functions and queries, command line options to run db automation procedures and so on. I'm also not sure, about which mechanisms or technologies are available in MS SQL Server 2008 Express edition.