SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE people(
  id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL
);
INSERT INTO people(first_name, last_name) VALUES
  ('Alice', 'Jackson'),
  ('Bob', 'Lynch');
2 rows affected

SELECT * FROM people;


id
first_name
last_name


1
Alice
Jackson

2
Bob
Lynch


MERGE INTO people
USING (VALUES
  (1, 'Alice', 'Turner'),
  (3, 'Charlie', 'Ross')
) friends(id, first_name, last_name)
ON people.id = friends.id
WHEN NOT MATCHED THEN
  INSERT (first_name, last_name) VALUES (friends.first_name, friends.last_name)
WHEN MATCHED THEN
  UPDATE SET people.first_name = friends.first_name,
             people.last_name = friends.last_name
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;
3 rows affected

SELECT * FROM people;


id
first_name
last_name


1
Alice
Turner

3
Charlie
Ross