sjimmo (6/20/2013)
If the data is going one way, from DB A to DB B and not going the other way the change the tables using identity fields by dropping the identity option of DB B. Instead of using a stored procedure and C# application to move data, which can place an added load on the process, use SQL Server Transactional Replication. This will keep DB B looking like DB 1. Maintenance is low and transactions are quick. Replication operates on data manipulations which show up in the transaction log and do not need to search using a select for any data changes which you are probably doing with your stored procedure.
I've never used transaction replication so thanks for the idea, I'll do some reading and see if it can help in this situation. But I do not have have the option of dropping any identities. For the sake of argument, assume we have 6 possible dbs. The business requirement is that a person (all their data across all tables) can be moved from the db they are currently 'homed' in to any other db.
These db's may be on the same server or different servers. As the system grows new db's will be added as necessary. Our master index holds the configuration and connection information so we know the pool of dbs, where they are located, and the connection information for each db. Basically we are load balancing our data. There are business and technical reasons for doing this.