MERGE :

Merge statement introduced in MSSQL server 2008. Using merge statement we can include multiple DML operations logic in one statement. It Performs insert, update, or delete operations on a target table based on the results of a join with a source table.

MERGE syntax consists of five primary clauses :

 1. MERGE clause specifies the table or view that is the target of the insert, update, or delete operations.
 2. USING clause specifies the data source being joined with the target.
 3. ON clause specifies the join conditions that determine where the target and source match.
 4. WHEN clauses (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the ON clause and any additional search criteria specified in the WHEN clauses.
 5. OUTPUT clause returns a row for each row in the target that is inserted, updated, or deleted.

Example : If records are exists in users table then we have to update the records otherwise we have to insert into users table.

-- creating temp table as source
CREATE TABLE #testmerge 
  ( 
     username VARCHAR(10), 
     limitamt NUMERIC(20, 6) 
  ) 

-- populating the source table
INSERT INTO #testmerge 
VALUES     ('satya', 
            20000.000000), 
            ('jyothi', 
             22000.000000), 
            ('suresh', 
             30000.000000), 
            ('gopal', 
             44000.000000), 
            ('ram', 
             20000.000000) 

-- updating / inserting the users table records using MERGE statement
MERGE users AS target 
using #testmerge AS source 
ON target.username = source.username 
WHEN matched THEN 
  UPDATE SET limitamt = source.limitamt 
WHEN NOT matched THEN 
  INSERT (username, 
          limitamt) 
  VALUES (source.username, 
          source.limitamt);