synchronization of two databases
-
Hi, I have two identical databases whitch are different only by name (DB1 and DB2) Eatch database is on one computer (PC1 and PC2) into a network. Database DB1 contains table Tab1. Database DB2 contains table Tab2. The two tables Tab1 and Tab2 are identical as structure an receive the same data from an external aplication. There are moments when the network is interrupted, from different causes and the two tables no longer contains the same data. To synchronize the tables I have to copy only the rows which are lost from Tab1 in Tab2. By a stored procedure i have to know which are the numbers of rows lost from Tab2 to fill in with rows from Tab1 I actually have to copy a number of lines from Tab1 in Tab2 with the condition to know which are the rows lost from Tab2. Someone knows how can I do that? Thanks.
-
Hi, I have two identical databases whitch are different only by name (DB1 and DB2) Eatch database is on one computer (PC1 and PC2) into a network. Database DB1 contains table Tab1. Database DB2 contains table Tab2. The two tables Tab1 and Tab2 are identical as structure an receive the same data from an external aplication. There are moments when the network is interrupted, from different causes and the two tables no longer contains the same data. To synchronize the tables I have to copy only the rows which are lost from Tab1 in Tab2. By a stored procedure i have to know which are the numbers of rows lost from Tab2 to fill in with rows from Tab1 I actually have to copy a number of lines from Tab1 in Tab2 with the condition to know which are the rows lost from Tab2. Someone knows how can I do that? Thanks.
Hi Do you rows have an unique identifier? If yes you could use something like this: insert into tab2 select * from tab1 where not exists (select table2.id from tab2 as table2 where table2.id = tab1.id) This will only work for added rows. Changing existing rows and synchronizing deleted rows will not work with this. Regards Sebastian
It's not a bug, it's a feature! Check out my CodeProject article Permission-by-aspect. Me in Softwareland.
-
Hi, I have two identical databases whitch are different only by name (DB1 and DB2) Eatch database is on one computer (PC1 and PC2) into a network. Database DB1 contains table Tab1. Database DB2 contains table Tab2. The two tables Tab1 and Tab2 are identical as structure an receive the same data from an external aplication. There are moments when the network is interrupted, from different causes and the two tables no longer contains the same data. To synchronize the tables I have to copy only the rows which are lost from Tab1 in Tab2. By a stored procedure i have to know which are the numbers of rows lost from Tab2 to fill in with rows from Tab1 I actually have to copy a number of lines from Tab1 in Tab2 with the condition to know which are the rows lost from Tab2. Someone knows how can I do that? Thanks.
Use this: Case 1
Insert into DB1.dbo.Table1 select * DB2.dbo.Table2 from where DB2.dbo.Table2.ID not in (select DB1.dbo.Table1.ID from DB1.dbo.Table1 )Case 2:Insert into DB2.dbo.Table2 select * DB1.dbo.Table1 from where DB1.dbo.Table1.ID not in (select DB2.dbo.Table2.ID from DB2.dbo.Table2)
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com
-
Use this: Case 1
Insert into DB1.dbo.Table1 select * DB2.dbo.Table2 from where DB2.dbo.Table2.ID not in (select DB1.dbo.Table1.ID from DB1.dbo.Table1 )Case 2:Insert into DB2.dbo.Table2 select * DB1.dbo.Table1 from where DB1.dbo.Table1.ID not in (select DB2.dbo.Table2.ID from DB2.dbo.Table2)
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com
Thank you all. The problem is that databases DB1 and DB2 are on different computers.
-
Hi, I have two identical databases whitch are different only by name (DB1 and DB2) Eatch database is on one computer (PC1 and PC2) into a network. Database DB1 contains table Tab1. Database DB2 contains table Tab2. The two tables Tab1 and Tab2 are identical as structure an receive the same data from an external aplication. There are moments when the network is interrupted, from different causes and the two tables no longer contains the same data. To synchronize the tables I have to copy only the rows which are lost from Tab1 in Tab2. By a stored procedure i have to know which are the numbers of rows lost from Tab2 to fill in with rows from Tab1 I actually have to copy a number of lines from Tab1 in Tab2 with the condition to know which are the rows lost from Tab2. Someone knows how can I do that? Thanks.
-
Thank you all. The problem is that databases DB1 and DB2 are on different computers.
-
Hi, I have two identical databases whitch are different only by name (DB1 and DB2) Eatch database is on one computer (PC1 and PC2) into a network. Database DB1 contains table Tab1. Database DB2 contains table Tab2. The two tables Tab1 and Tab2 are identical as structure an receive the same data from an external aplication. There are moments when the network is interrupted, from different causes and the two tables no longer contains the same data. To synchronize the tables I have to copy only the rows which are lost from Tab1 in Tab2. By a stored procedure i have to know which are the numbers of rows lost from Tab2 to fill in with rows from Tab1 I actually have to copy a number of lines from Tab1 in Tab2 with the condition to know which are the rows lost from Tab2. Someone knows how can I do that? Thanks.
-
Which database engine are you using. If you are using MS-SQL Server, it is fairly simple using linked server to keep both the dB in sync using some simple queries and statements...
Yes, DB1 is MSSQL 2005 and DB2 is MSSQL2000. I haven't experience with "linked server" Can you indicate some examples. Thanks. Regards, Robert
-
Yes, DB1 is MSSQL 2005 and DB2 is MSSQL2000. I haven't experience with "linked server" Can you indicate some examples. Thanks. Regards, Robert
You can get good details from the following URL http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx Let me know, if you have any difficulty
-
Which database engine are you using. If you are using MS-SQL Server, it is fairly simple using linked server to keep both the dB in sync using some simple queries and statements...
Try this to compare and sync two sql server database D-Softs Database Compare Tool