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

-- 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
	WHERE ID = 0;
	
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;