1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- Create a Table with New/Updated Records
DECLARE @records TABLE (
 ID int,
 FirstName varchar(50),
 LastName varchar(100),
 Salary int,
 Role int
)

INSERT INTO @records VALUES (0, 'Rob', 'Cooper', 1000000, 5); -- Yeah, Right.
INSERT INTO @records VALUES (0, 'John', 'Guido', 30000, 2);
INSERT INTO @records VALUES (2, 'Rachel', 'Jackson', 14750, 1);
INSERT INTO @records VALUES (9, 'Jon', 'Caton', 32000, 4);

INSERT INTO Staff
	SELECT FirstName, LastName, Salary, Role FROM @records R
	WHERE ID = 0 AND NOT EXISTS
	(SELECT * FROM Staff S
		WHERE S.FirstName = R.FirstName AND
			S.LastName = R.LastName AND
			S.Salary = R.Salary AND
			S.Role = R.Role);
	
UPDATE S
	SET FirstName = R.FirstName,
		LastName = R.LastName,
		Salary = R.Salary,
		Role = R.Role
	FROM Staff S
	INNER JOIN @records R
	ON S.ID = R.ID;
	
SELECT * FROM Staff;