SQL Server Performance Forum – Threads Archive
Add/update data from one server to another
Help, I’m the new DBA and I’ve been tasked with doing the following. This is the scenario! I have a SQL server database on one server (lets call it Database_A), and another one on different server (Database_A_Hist that’s exactly the same in structure). Database_A_Hist is a dedicated history SQL server database that contains the same information as Database_A and every night a job is supposed to run that will first check that the information in Database_A matches Database_A_Hist. If not then the affected information is added or updated to the Database_A_Hist server. I can do this easily in VB6, but they need this done on the servers. Does anyone have a SQL script that will do this? ThanksHi,
well it could be done using Merge Replication also if DATABASE STRUCTURE IS SAME. Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami
Yes the database structure is exactly the same. If you arent part of the solution, then there is good money to be made prolonging the problem
Hi,<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by [email protected]</i><br /><br />Yes the database structure is exactly the same.<br /><br />If you arent part of the solution, then there is good money to be made prolonging the problem<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />i didn’t get u on this !<br />well and another mathod is to do it with log shipping , please refer Replication (Merge,Transactional and Snapshot ) and choose appropriate solution as per suitable.<br /><br />in your case Transactional Replication will server as you wants information from Database_A to Database_hist and if it has some updated information it will update / add it into Database_hist.<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />
Thanks<br />So you think Transactional Replication is the best best. I have been reading up on it.<br /><br />Some more info…<br />The data on Database_A will get deleted every 6 weeks, but this should not affect Database_A_Hist. If any new data after that comes on, then Database_A_Hist will be either updated with this data or inserted if it’s new information.<br /><br /><b>Please note* Updated data on Database_A should not get inserted into Database_A_Hist. It should be updated.</b>[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br />Regards<br /><br />Ali<br /><br />[email protected]<br /><br />If you arent part of the solution, then there is good money to be made prolonging the problem
Hi,<br /><br />I would suggest you to read <br /><a href=’http://www.mssqlcity.com/Articles/Replic/SetupTR/SetupTR.htm’ target=’_blank’ title=’http://www.mssqlcity.com/Articles/Replic/SetupTR/SetupTR.htm'<a target="_blank" href=http://www.mssqlcity.com/Articles/Replic/SetupTR/SetupTR.htm>http://www.mssqlcity.com/Articles/Replic/SetupTR/SetupTR.htm</a></a> and <br /><br /><a href=’http://www.sql-server-performance.com/tp_replication.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/tp_replication.asp'<a target="_blank" href=http://www.sql-server-performance.com/tp_replication.asp>http://www.sql-server-performance.com/tp_replication.asp</a></a> and<br /><br /><a href=’http://www.sql-server-performance.com/transactional_replication.asp’ target=’_blank’ title=’http://www.sql-server-performance.com/transactional_replication.asp'<a target="_blank" href=http://www.sql-server-performance.com/transactional_replication.asp>http://www.sql-server-performance.com/transactional_replication.asp</a> </a> <br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />
assuming you have a unique id column on the tables with colA in my example being the id column you could, insert into Database_A_Hist (colA, colB, …)
select colA, colB, ….
from Database_A
where not exists
(select * from Database_A_Hist
where Database_A.colA = Database_A_Hist.colA) I have several jobs at night that use this syntax to push new products out from my centralized database to my 3 front end databases.
Thanks I’ll try this out. If you arent part of the solution, then there is good money to be made prolonging the problem
]]>