Automatic Database Synchronisation

Question

Hi all,

I am using SQL Server 2008 and Visual Studio 2008

I have two databases.
I need to run a query in a database and grab those results and update a table inside another database.
This process would be done every one hour automatically.

How can I do it? Data Transmission Services(DTS) used from SQL 2000?
Is there something better than that?

Or shall I write a windows service to do that?
What are the alternatives?

Is there a tutorial handy?

Thank you!

Answer 1

Hi Naveen

you can do 2 work:

1- use replication in sql server 2- write an application that do this work for you. I suggest that use from bcp (bulk copy) for doing this work.

Answer 2

If you have two databases within the same SQL server instance, you could write a stored procedure and use a SQL Server 'job' to schedule this stored procedure.