Synchronise the data in two applications db

[Guest]

There are 2 applications which run on same cf server and  same sql server with different database for each(say DB1 and DB2).I have a requirement
to synchorinise the data from Tabl1 of first application to the Table2 of second application?
What would be the best method to achieve this?
Thanks

[Owainnorth]

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

[Guest]

The tables already exists,hence we cannot change that. At the moment, data is entered via 2 applications

to make it same. Currently, the first application's table(Table A) contain 200 which is the correct data count while
second contains 100(Table B) .
The objective is to make both tables contain 200 records and in future while a record is inserted in Table A,
it is also saved to Table B which is in other DB on the same SQL server. Both application run on same cf server.

[Dan_Bracuk]

Have both applications write to the same table.  Once you have that set up, you only have to synchronize the data once.

[Guest]

That's is a good solution to point to one table in both applications, but for this I need to change the whole references where that table is used in the entire

application(Its a big application). Can I have any other way?

[Owainnorth]

Personally, I'd do a one-off job to merge the data, then use database triggers on insert, update and delete to keep the two in sync.

[Dan_Bracuk]

If the tables have the same name, couldn't you simply edit the datasource on the admin page?

[JMF3]

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

[JR__Bob__Dobbs]

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.