Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rohitmathur11
Yak Posting Veteran
77 Posts |
 Posted - 2010-01-29 : 04:06:53
|
| Hi,I need to get data in to one database from another database... Like in one database ABC table A is there ...i am having another database XYZ here also table A (same structure) . when ever insert/update happen on table A(ABC) ..same data will go in to table A(XYZ) .. For this i can write one trigger on ABC and by using Linked server i can insert data in to table A (XYZ)....but i can not do any thing on ABC database...I have to do get data from XYZ database only ...like i need to take data from ABC.. How to do this ... stored procedure or .....Ant idea... |
|
|
rohitmathur11
Yak Posting Veteran
77 Posts |
Posted - 2010-01-29 : 04:26:29
|
| Both database are on different server |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 04:53:55
|
| Replication? |
|
|
|
rohitmathur11
Yak Posting Veteran
77 Posts |
Posted - 2010-01-29 : 05:04:27
|
| This is for only one table .. i need to get data from another database which is on different server .. and i need to insert / update data when ever there is an insert/update on source table ..Is this possible by replication ..can we use one stored procedure ...which will connect to remote database by using linked server ..and get data ..? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 05:10:11
|
| "Is this possible by replication .."Yes ... might not be worth the hassle though.Trouble with Trigger is that the insert to other server has to happen within the current transaction, if the COMMs are down then all bets are off, if the operation is slow it will be locking the table / blocking other users.But other than that a trigger is not a bad idea - e.g. if you infrastructure is fast and reliable.An SProc would be OK (but risk that the table is modified outside the SProc).Sproc can do:Insert locallyInsert remotelyYou can do that within a single transaction (so you can roll back the Local insert, if you have to) but that will lock/block locally.Or you insert locally and remotely without a transaction - then if the remote transaction fails then what? If the record is already there you could delete the local insert you just made ... but what if the COMMs are down - your remote insert will probably fail with an application error - you then have a locally inserted record, no record on remote, and no means of catching the error.Service Broker maybe? |
|
|
|
rohitmathur11
Yak Posting Veteran
77 Posts |
Posted - 2010-01-29 : 05:26:35
|
| i am going to write stored procedure on the destination database ..to get data from source database ...by usgin linked server ...is it ok ...can we do this ...is this better way ...or any other way |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-29 : 05:50:54
|
| Your choice! Hopefully I've explained the Pros and Cons that I could think of. |
|
|
|
|
|
|
Getting data from different server