Merge Two Tables

Question

In SQL Server 2008 R2 I have a master table called Employees that holds about 20 fields. I have a second spreadsheet that has all the fields except 5 called UpdateToEmployees that I need to merge with Employees but only where the values are not the same.

What is the most efficient way to do this? My thought process (which may be the long way) is to:

1. Run A Select statement to get the rows where the values are not the same
2. Take that result set and insert into a temp table flagging a field exists with a yes/no value
3. If the field exists contains a yes value - run an update query
4. If the field exists contains a no value - run an insert query

Using the DDL below - how could I join on empID and update the records where when matched = Yes or True (whatever it is that it returns) and insert the records that currently do not exist in Employees

Create Table Employees
(
  empid varchar(50)
  ,empaddress varchar(500)
  ,phone varchar(12)
  ,emcontact1 varchar(500)
  ,emcontact2 varchar(500)
  ,active varchar(10)
  ,manager varchar(50)
  ,paygrade int
)

Insert Into Employees (empid,empaddress,phone,emcontact1, emcontact2, active, manager, paygrade) VALUES
('aa11', '123 blue', '1234567890', 'A A', 'B B', 'Yes', 'Manager 1', 1)
,('bb22', '123 green', '4567890000', 'B B', 'A A', 'Yes', 'Manager 2', 1)
,('cc33', '123 red', '789123456', 'E E', 'R R', 'Yes', 'Manager 3', 2)

Create Table UpdateToEmployees
(
  empid varchar(50)
  ,empaddress varchar(500)
  ,phone varchar(12)
)

Insert Into UpdateToEmployees (empid, empaddress, phone) VALUES
('aa11', '611 pink', '9999999999')
,('cc33', '212 orange', '0000000000')
,('ee99', '1411 red', '4444444444')
,('zz22', '2212 fox', '5555555555')
,('tt83', '1999 prince', '7777777777')

Answer 1

UNTESTED
as I do not have access to SQL Server at the moment, but try this, it should work, or at least point you in the right syntax direction for a Merge statement

MERGE Employees emp
USING UpdateToEmployees ute
ON [emp].[empid] = [ute].[empid]
WHEN MATCHED THEN
  UPDATE
  SET [emp].[empaddress] = [ute].[empaddress]
  ,[emp].[phone] = [ute].[phone]
WHEN NOT MATCHED BY TARGET THEN
INSERT (empid, empaddress, phone)
VALUES ([ute].[empid], [ute].[empaddress], [ute].[phone]);

Answer 2

Since SQL Server 2008 the MERGE statement has been available, which will be more efficient and a hand-made "check and then update or insert" procedure. See https://msdn.microsoft.com/en-us/library/bb510625.aspx for reference, though you might find it easier to find a tutorial rather than working from that (http://www.made2mentor.com/2013/05/writing-t-sql-merge-statements-the-right-way/ is the first that came up from my cursory search).