Using MERGE in SQL Server 2012 to insert/update data

Question

I am using SQL Server 2012 and have two tables with identical structure. I want to insert new records from table 1 to table 2 if they don't already exist in table 2.

If they already exist, I want to update all of the existing records in table 2.

There are some 30 columns in my tables and I want to update all of them.

Can someone please help with this? I had a look at various links posted over internet, but quite don't understand how my statement should look like.

Answer 1

It's really not that hard....

You need:

• a source table (or query) to provide data
• a target table to merge it into
• a condition on which those two tables are checked
• a statement what to do if a match (on that condition) is found
• a statement what to do if NO match (on that condition) is found

So basically, it's something like:

-- this is your TARGET table - this is where the data goes into    
MERGE dbo.SomeTable AS target       
-- this is your SOURCE table where the data comes from 
USING dbo.AnotherTable AS source    
-- this is the CONDITION they have to "meet" on
ON (target.SomeColumn = source.AnotherColumn)  

-- if there's a match, so if that row already exists in the target table,
-- then just UPDATE whatever columns in the existing row you want to update
WHEN MATCHED THEN                           
    UPDATE SET Name = source.Name,
               OtherCol = source.SomeCol

-- if there's NO match, that is the row in the SOURCE does *NOT* exist in the TARGET yet,
-- then typically INSERT the new row with whichever columns you're interested in
WHEN NOT MATCHED THEN  
    INSERT (Col1, Col2, ...., ColN)  
    VALUES (source.Val1, source.Val2, ...., source.ValN);