Merge syntax in SQL Server 2008

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.
LVL 52
Dale FyeOwner, Developing Solutions LLCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LajuanTaylorCommented:
The following Microsoft examples for Inserting, Updating, and Deleting Data by Using MERGE with SQL Server 2008R2 might be what you're looking for.
https://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx

Can you provide a sample create statement for your table structure? I'm pondering two options for you to avoid having to explicitly list all fields... one approach involves using SQL schema information and the other using a hashbytes function.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
SQL script attached.

The ID field will be the join field.  It is an AutoNumber in the source Access table.  MY thought was to do something along the lines of:

MERGE Target AS T
USING (SELECT MWL.* FROM tbl_Master_Well_List_Staging as MWL
              WHERE MWL.Thru_Date IS NULL) AS S
ON (T.ID = S.ID)
WHEN NOT MATCHED BY TARGET
    THEN INSERT T.* VALUES(S.*)
WHEN MATCHED
    THEN UPDATE SET T.* = S.*
WHEN NOT MATCHED BY SOURCE
    THEN DELETE
OUTPUT $action, Inserted.*, Updated.*, Deleted.*;
2015-07-05-MWL-Staging-Script.sql
Olaf DoschkeSoftware DeveloperCommented:
You can't use a universal syntax with .*

The MERGE Statement has to be something like in the first example given at Technet:

MERGE Target AS T
USING Source AS S
ON (T.ID = S.ID) 
WHEN NOT MATCHED BY TARGET
    THEN INSERT (Field1, Field2,...) VALUES (S.Field1, S.Field2,...)
WHEN MATCHED 
    THEN UPDATE SET T.Field1 = S.Field1, T.Field2 = S.Field2,...
WHEN NOT MATCHED BY SOURCE
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;

Open in new window


The inserts you write are almost normal SQL statements, just the target table is already known from the first line of the MERGE. The match optimization makes it faster than single inserts and updates, but you still specify statements for single record inserts or updates, not something like
WHEN NOT MATCHED BY TARGET INSERT INTO T SELECT * FROM S.

Besides, if you really do as that, you finally have T==S, while the typical merge operation does merge eg new and updated products, it does not DELETE old products, ie the source does not contain unchanged data. That means you don't have UPDATES really doing nothing, but you also don't DELETE anything, just because it's not in the source (changes). Instead your source may have a bit field indicating update or delete for matches or you do a second merge with a list of source.IDs only, that should be deleted, then you do the DELETE operation WHEN MATCHED.

Bye, Olaf.
LajuanTaylorCommented:
@Dale Fye (Access MVP) - Is "tbl_Master_Well_List" the table in MS Access or SQL Server? I understand that the Current and Archived tables reside in SQL Server.

What criteria determines if a record is Current, Archived, or Deleted? I'm assuming:
A new record in "tbl_Master_Well_List" is inserted into "Current"
An active record in "tbl_Master_Well_List" is updated in "Current"
An inactive record in "tbl_Master_Well_List" is moved to "Archived" if found in "Current"
An active record in "tbl_Master_Well_List" is moved to "Current" if found in "Archived"

On a side note, have you considered asking the
BI software experts
to create an SSIS package that you can run on-demand which will take care of migrating the tbl_Master_Well_List between the current and archived tables?

SSIS can perform the heavy lifting of integrating the MS Access and SQL Data...
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Thanks for the help.  Real PITA to copy all of those fields into the Insert and Update portions, but it is working great.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.