title: SQL Sync database tables to another server
body: <p>we have an system that creates and table in a database on our production server for each day/shift. I would like to somehow grab the data from that server and move it to our archive server and if the data is more than x days old remove it off the production server. </p>
  
  <p>On the production server, the database is called "transformations" and the tables are named "yyyy-mm-dd_shift_table". I would like to move this into a database on another server running SQL 2012 into a database "Archive" with the same name. Each table contains about 30k records for the day. </p>
  
  <p>The way i see it would be something like:</p>
  
  <ul>
  <li>Get list of tables on Production Server</li>
  <li>If table exists in Archive server, look for any changes (only really relevant for the current table) and sync changes</li>
  <li>If table doesn't exist in Archive Server, create table and syn changes.</li>
  <li>If date on table is greater and X days, delete table from archive server</li>
  </ul>
  
  <p>Ideally i would like to have this as a procedure in SQL that can run either daily/hourly ect.</p>
  
  <p>Suggestions on how to attack this would be great.</p>
  
  <p>EDIT: Happy to do a select on all matching tables in the database and write them into a single table on my database.</p>