Synchronise the data in two applications db

You might try re-creating TableB as a view of TableA.  Assuming that all the columns are the same the following might work:

1. Stop all transaction processing in the two tables.

2. Merge all data into TableA.

3. Drop table TableB.

4. Re-create TableB as an updateable view of TableA.

5. As time allows modify your applications to use only TableA.

This will get all the records matched up, pulling from DB2 into DB1 and

then from DB1 into DB2:

INSERT INTO DB1.dbo.Tabl1

SELECT *

FROM DB2.dbo.Tabl2

WHERE ID NOT IN (

SELECT ID

FROM DB2.dbo.Tabl2

)

INSERT INTO DB1.dbo.Tabl2

SELECT *

FROM DB2.dbo.Tabl1

WHERE ID NOT IN (

SELECT ID

FROM DB2.dbo.Tabl1

)

If you need to synchronize changes to records that exist in both databases,

then you're going to have to decide which one is the 'authority' in each

case before doing UPDATE queries. An UPDATE of this sort can look sort of

like so:

UPDATE target

SET Fld1 = src.Fld1,

Fld2 = src.Fld2,

Fld3 = src.Fld3,

...

FROM DB1.dbo.Tabl1 AS target INNER JOIN

DB2.dbo.Tabl2 AS src ON target.ID = src.ID

Create the table in only one of the databases, and grant permissions to it for the other user.