Reconciling a column across two tables in SQL Server

Question

There are two Databases, Database A has a table A with columns of id, group and flag. Database B has a table B with columns of ID and flag. Table B is essentially a subset of table A where the group == 'B'.

They are updated/created in odd ways that are outside my understanding at this time, and are beyond the scope of this question (this is not the time to fix the basic setup and practices of this client).

The problem is that when the flag in Table A is updated, it is not reflected in table B, but should be. This is not a time-critical problem, so it was suggested I create a job to handle this. Maybe because it's the end of the week, or maybe because I've never written more than the most basic stored procedure (I'm a programmer, not a DBA), but I'm not sure how to go about this.

At a simplistic level, the stored procedure would be something along of the lines of

Select * in table A where group == B

Then, loop through the resultset, and for each id, update the flag.

But I'm not even sure how to loop in a stored procedure like this. Suggestions? Example code would be preferred.

*Complication: Alright, this gets a little harder too. For every group, Table B is in a separate database, and we need to update this flag for all groups. So, we would have to set up a separate trigger for each group to handle each DB name.

And yes, inserts to Table B are already handled - this is just to update flag status.

Answer 1

There appears to be a similar question at http://stackoverflow.com/questions/149132/how-can-one-iterate-over-stored-procedure-results-from-within-another-stored-pr , but it stops a step short of what I am looking to accomplish.

Answer 2

Create an update trigger on table A that pushes the necessary changes to B as A is modified.

Basically (syntax may not be correct, I can't check it right now). I seem to recall that the inserted table contains all of the updated rows on an update, but you may want to check this to make sure. I think the trigger is the way to go, though.

create trigger update_b_trigger
on Table_A
for update
as
begin
   update Table_B
   set Table_B.flag = inserted.flag
   from inserted
          inner join Table_B
             on inserted.id = Table_B.id 
                 and inserted.group = 'B'
                 and inserted.flag <> Table_B.flag
end

[EDIT] I'm assuming that inserts/deletes to Table B are already handled and it's just flag updates to Table B that need to be addressed.

Answer 3

Assuming that ID is a unique key, and that you can use linked servers or some such to run a query across servers, this SQL statement should work (it works for two tables on the same server).

UPDATE Table_B
SET Table_B.Flag = Table_A.Flag
FROM Table_A inner join Table_B on Table_A.id = Table_B.id

(since Table_B already contains the subset of rows from Table_A where group = B, we don't have to include this condition in our query)

If you can't use linked servers, then I might try to do it with some sort of SSIS package. Or I'd use the method described in the linked question (comments, above) to get the relevant data from Database A into a temp table etc. in Database B, and then run this query using the temp table.

Answer 4

UPDATE 
  DatabaseB.dbo.Table_B
SET 
  DatabaseB.dbo.Table_B.[Flag] = DatabaseA.dbo.Table_A.Flag
FROM 
  DatabaseA.dbo.Table_A inner join DatabaseB.dbo.Table_B B 
  on DatabaseA.dbo.id = DatabaseB.dbo.B.id

Complication: For sevaral groups run one such update SQL per group.

Note you can use Flag without []. I'm using the brackets only because of syntax coloring on stackoverflow.