Showing posts with label deleted. Show all posts
Showing posts with label deleted. Show all posts

Monday, November 24, 2008

How to copy data from one table of SQL server 2005 to the other of SQL server 2005?

There was a strange problem where in one had to keep the two SQL tables of SQL server 2005 located on two different machines of in synch with each other. Whenever there is an update in either of the tables the other table should contain the respective information.
One way to achieve this is to use triggers, but they failed in this scenario (where the two tables are located on two different machines)
This is where we thought of using the feature of ‘maintenance plan’ in SQL server 2005. In this one needs to add a task called ‘Execute T-SQL statement task’ and enter the stored procedure or a query. Whenever the data is inserted into table 1, through triggers it is inserted into the temporary table from which the query in the maintenance plan picks up and inserts the same into the other table. A similar maintenance plan needs to be created on the other SQL server 2005 which will insert into table 1. The maintenance plan runs at regular intervals.
The records affected by an insert, update and a delete statement can be obtained from inbuilt tables INSERTED and DELETED.
This approach so far may lead to recursion. In this case one will have to switch the triggers off before inserting data in the table through maintenance plan.