I have a set of 10 tables in a database (DB1). And there are 10 tables in another database (DB2) with exact same schema on the same SQL Server 2008 R2 database server machine.

The 10 tables in DB1 are frequently updated with data.

I intend to write a stored procedure that would run once every day for synchronizing the 10 tables in DB1 with DB2. The stored procedure would make use of the MERGE statement.

Now, my aim is to make this as generic and parametrized as possible. That is, accommodate for more tables down the line... and accommodate different source and target DB names. Definitely no hard coding is intended.

This is my algorithm so far:

1. Have the database names as parameters
2. Have the first query within the stored procedure... result in giving the names of the 10 tables from a lookup table (this can be 10, 20 or whatever)
3. Have a generic MERGE statement that does the sync for each of the above set of tables (based on primary key?)

This is where I need more inputs on.

What is the best way to achieve this stored procedure? SQL syntax would be helpful.