Take the 2-minute tour ×

I have two databases, A, and B. On database A, I have a table X, with 10 columns in it. On database B, I have a table Y, with 7 columns in it. 4 of the columns from these tables match, and whenever one table updates one or more of these columns, I need the other table to update these columns. How can I do this? Replication wouldn't seem to work because the table structures are different, and insert/update triggers would seem to create infinite loops.

share|improve this question
How come replication didn't help? If you just publish the 4 matching tables you should have no problem. Or do you want to update more than the 4 matching tables? –  Eton B. Aug 24 '10 at 17:53
@Eton B.: Sorry, I think I was unclear on that. There are 2 tables, and 4 of the columns match on them, with unrelated columns on the side. If it was just 4 tables that matched, replication would work fine. –  Brisbe42 Aug 24 '10 at 17:55
add comment

3 Answers

up vote 1 down vote accepted

To avoid the loops you could have your triggers not do an update if the values are equal?

share|improve this answer
add comment

Replication works fine on tables with different structures, see Filtering Published Data.

As for triggers to avoid infinite loop, you would use context information to set up that you're currently in a 'replication' trigger so that you'd avoid looping, see Using Session Context Information:

  • in the trigger, you check if CONTEXT_INFO() says you're already in a trigger.
    • if YES, do nothing (return)
    • if NO, SET CONTEXT INFO to reflect your operation
  • copy the data
  • when the 'replica' trigger fires, will find your context info and do nothing
  • clear context info
  • return
share|improve this answer
add comment

select * into NewTable from PastTable

share|improve this answer
add comment

Your Answer


