December 10, 2013 at 4:05 am
hello
I've two databases with different structures, i want to add a record with it's data from the first database into the second database in the place i define, this action must happen automatically as soon as the record is created in the first database.
any suggestions?
December 10, 2013 at 4:29 am
Sounds like a situation that will be answered by a trigger. You can do a cross database query pretty easily if they're on the same server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 10, 2013 at 4:54 am
thank you for the reply
can you give a sample of the procedure , they're on different servers
and what if the fields are different types in each database
December 10, 2013 at 5:45 am
Different servers makes it more difficult. In order to use a trigger you'd have to use a linked server. It sounds less viable now than it did. The different data types is only a problem if you can't convert between them.
What about just modifying the code so that it does an insert to each server? That would work better.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 10, 2013 at 6:38 am
Grant Fritchey (12/10/2013)
What about just modifying the code so that it does an insert to each server? That would work better.
As Grant suggests, this would be the ideal situation.
In the event this is impossible you can quite easily implement a remote procedure call over a linked server to do this (as suggested by Grant as well). Logically the trigger would call this remote procedure. In the event the trigger fails for any reason it could raise an error if required to roll back the original insertion.
Depending on the network connection this could easily complete in a fraction of a second.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply