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')

2 Answers 2


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]
  SET [emp].[empaddress] = [ute].[empaddress]
  ,[emp].[phone] = [ute].[phone]
INSERT (empid, empaddress, phone)
VALUES ([ute].[empid], [ute].[empaddress], [ute].[phone]);
  • It worked exactly as I needed, thank you! I also like how you added the brackets around table aliases and field names, something I need to get into the habit of doing. Oct 5, 2016 at 14:08
  • Why all these square brackets? Leaving them out won't affect the query's ability to work in the slightest. (You can be sure about that without any testing.)
    – Andriy M
    Oct 5, 2016 at 21:11

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.