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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

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

-- Iterate Through the Records
-- INSERT/UPDATE as necessary.
DECLARE @id int;
SET @id = -1;

WHILE (@id IS NOT NULL)
BEGIN
	SET @id = (SELECT TOP 1 ID FROM @records);
	IF (@id IS NOT NULL)
	BEGIN
		IF (@id = 0)
		BEGIN
			INSERT INTO Staff (FirstName, LastName, Salary, Role)
				SELECT TOP 1 FirstName, LastName, Salary, Role FROM @records;
		END
		
		ELSE
		BEGIN
			DECLARE @firstName varchar(50);
			DECLARE @lastName varchar(100);
			DECLARE @salary int;
			DECLARE @role int;
			
			SET @firstName = (SELECT TOP 1 FirstName FROM @records);
			SET @lastName = (SELECT TOP 1 LastName FROM @records);
			SET @salary = (SELECT TOP 1 Salary FROM @records);
			SET @role = (SELECT TOP 1 Role FROM @records);
			
			UPDATE Staff
			SET FirstName = @firstName,
				LastName = @lastName,
				Salary = @salary,
				Role = @role
			WHERE
				ID = @id;
		END
	END
	DELETE TOP (1) FROM @records;
END

SELECT * FROM Staff;