August 4, 2011 at 12:52 am
Hi guys,
I need to update data from one table in database A to another table in database B. The requirement is to update it automatically at 12:00 in the mid-night everyday. Database A is MS SQL Server 7, and database B is Oracle database.
What's the best to implement it? I thinking to use trigger, am I right? Can anyone help with basic idea to schedule the job
Thanks in advance for any advice.
August 4, 2011 at 6:52 pm
Since you have SQL Server and Oracle a trigger is out of the question.
You could create an SSIS Package and schedule it to run at the specified time.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 6, 2011 at 12:15 pm
Define Oracle Database as a linked server of SQL Server database.
Write the script needed to update as required by business rules.
Schedule a job that runs the script, be sure you set an alert in case of failure.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 6, 2011 at 1:04 pm
In addition to Linked Servers you have other options if you choose not to create a Linked Server.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 6, 2011 at 1:23 pm
I will second the SSIS option - it will perform better than using linked servers to update and won't require pulling the data across the linked server to a temp table first.
If you use a linked server and try to update directly, you need to make sure you can run distributed transactions across the linked servers which may not work.
So, use SSIS to extract from one and load into the other.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 6, 2011 at 2:35 pm
Jeffrey Williams-493691 (8/6/2011)
I will second the SSIS option - it will perform better than using linked servers to update and won't require pulling the data across the linked server to a temp table first.
Good point I did not consider the temp table issue.
I'm biased towards using SSIS but if there is a better alternative then I would go with it.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 6, 2011 at 7:51 pm
I am biased towards SSIS also...found I can get much better performance and manage space using SSIS much easier than using linked servers.
That is the other thing I forgot to mention, with SSIS you can control the batch size and commit size - which can reduce the usage of the transaction log.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 7, 2011 at 12:56 am
Jeffrey Williams-493691 (8/6/2011)
I am biased towards SSIS also...found I can get much better performance and manage space using SSIS much easier than using linked servers.That is the other thing I forgot to mention, with SSIS you can control the batch size and commit size - which can reduce the usage of the transaction log.
I am trying to find one and I don't think there is a better alternative 😀 , whats the OP requires is exactly what SSIS was made for , altough SSIS could be replaced with any ETL tool
August 7, 2011 at 6:48 am
Performance is definitely an issue when inserting a large number of records into a Oracle Table.
If you do not specify the number of records to commit Oracle does not do so well. I have found SQL Server to be more forgiving than Oracle when transferring large amounts of Data.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply