At the moment, I need to synchronize data between two SQL Server databases on separate servers but on the same network. As the schema between the tables that are to be synced differ, I've been developing a solution that utilizes a custom queue to transfer data. This involves triggers for each table that inserts the new/updated fields into a transfer queue table, to which a periodically run stored procedure via SQL Server Agent checks for entries, acts accordingly, then deletes those entries.
My question is whether or not it'd be more efficient to remove the transfer queue table and instead evoke the stored procedure via the trigger and transfer the data that way. Would the trigger simply call the stored procedure and complete or would it wait for the stored procedure to complete before exiting itself?