July 14, 2016 at 3:04 am
Hi Team,
Below is my scenario.
Server 1
Database Name : UNIFY
Table Name : User
No.of Columns : 50
String Datatype : Varchar
No.of Records : 10000
Server 2
Database Name : UNIFY
Table Name : User
No.of Columns : 60
String Datatype : NVarchar
No.of Records : 10000
- In Server 1 – when the no.of records = 10000, Database backup taken from server 1 and stored on Server 2.
- There are many transactions (INSERTS / UPDATES) done on Server 1.
- And there are few columns added in Server 2 Database.
- Now all the transactions (INSERTS / UPDATES) happened in Server 1 and the number of records in server 1 table is more than 20000, now i need to sync the server 2 table with server 1 table.
- Server 2 Database should be the final database for use.
what is the best method to sync the server 2 table with server 1 table.
i tried to sync using tablediff.exe, but due difference in structure, unable to sync the table.
July 14, 2016 at 7:16 am
Minnu (7/14/2016)
Hi Team,Below is my scenario.
Server 1
Database Name : UNIFY
Table Name : User
No.of Columns : 50
String Datatype : Varchar
No.of Records : 10000
Server 2
Database Name : UNIFY
Table Name : User
No.of Columns : 60
String Datatype : NVarchar
No.of Records : 10000
- In Server 1 – when the no.of records = 10000, Database backup taken from server 1 and stored on Server 2.
- There are many transactions (INSERTS / UPDATES) done on Server 1.
- And there are few columns added in Server 2 Database.
- Now all the transactions (INSERTS / UPDATES) happened in Server 1 and the number of records in server 1 table is more than 20000, now i need to sync the server 2 table with server 1 table.
- Server 2 Database should be the final database for use.
what is the best method to sync the server 2 table with server 1 table.
i tried to sync using tablediff.exe, but due difference in structure, unable to sync the table.
Since you want the two tables to be competely in sync:
1) EMPTY table 2. Truncate is preferred due to less logging or delete if you can't do truncate. If you delete all rows drop all indexes first then recreate after repopulate table.
2) Simply issue an INSERT statement that is hand coded to grab all rows and all columns that match and shove them into table 2. Poof, table 2 is exact copy of table 1 for all columns the same in the two.
If you need to do a MUCH more complex thing that involves leaving table 2 rows in place then you have THREE actions to complete. Each will have to be hand coded since your columns don't match up.
1) DELETE rows in table 2 that are no longer in table 1
2) UPDATE rows in table 2 that have same key value(2) in table 1. I won't bother with what happens if you can't uniquely identify a row. You will only be updating columns in both tables obviously.
3) INSERT new rows from table 1 in to table 2. I would use a NOT EXIST clause here on the key value(s).
BTW, you do those operations in that specific order to be most efficient.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply