I have an Access database that my client is using for a lot of their operations. Many, but not all of the tables are located on SQL Server. They have a business intelligence package which is linked to a SQL Server backend but because the database contains over 30M records, of which only about 6M are current, they have been advised by the BI software experts to split the backend data into Current Wells and Archived wells
We have a table (tbl_Master_Well_List) which contains the names and critical data for all of the wells (current and archived), but for speed purposes I need to regularly, and on demand, migrate the data from this table into the two SQL Server tables (Current and Archived). In that process, some of the entries in tbl_Master_Well_List will be new, some will have updates, and some will migrate either from Current to Archived or Archived to Current.
I believe I can do this with two Merge statements where I filter tbl_Master_Well_List on active wells and then use a MERGE statement to either append, update, or delete records in the SQL Current Wells table, and then do the same for the Archived Wells table. But I cannot seem to get the syntax for this operation correct. All of the fields in the three tables are identical in name and data type. Unfortunately, these tables contain over 50 fields and I would prefer to not have to explicitly list each of the fields in the append and update portions of the merge, if that is possible.