Thanks, this definitely set me in the correct direction.

The JOIN using BETWEEN is what I was missing. I started thinking about using a tally table but that was over complicating matters.

My completed code is below using permanent tables.

--Create the tables

IF OBJECT_ID('dbo.Cities', 'U') IS NOT NULL DROP TABLE dbo.Cities

CREATE TABLE Cities

(

City varchar (20),

CityRank int

)

IF OBJECT_ID('dbo.CityGroups', 'U') IS NOT NULL DROP TABLE dbo.CityGroups

CREATE TABLE CityGroups

(

CityGroup varchar(10),

CityDescription varchar(15),

RankStart int,

RankEnd int

)

IF OBJECT_ID('dbo.CityGroupmembers', 'U') IS NOT NULL DROP TABLE dbo.CityGroupmembers

CREATE TABLE CityGroupmembers

(

CityGroup varchar(10),

City varchar (20)

)

--Populate the tables

INSERT INTO Cities

SELECT 'Paris',1 UNION

SELECT 'Chicago',2 UNION

SELECT 'Seattle',3 UNION

SELECT 'Hong Kong',4 UNION

SELECT 'New York',5 UNION

SELECT 'Brasilia',6 UNION

SELECT 'Tangier',7 UNION

SELECT 'Berlin',8 UNION

SELECT 'Tokyo',9 UNION

SELECT 'Mexico City',10

INSERT INTO CityGroups

SELECT 'GroupA','Top 3 Cities',1,5 UNION

SELECT 'GroupB','Top 10 Cities',1,10 UNION

SELECT 'GroupC','Middle Eight',2,9

--Populate CityGroupmembers based on the current data in table Cities

--Insert data

INSERT INTO CityGroupmembers

SELECT --'Insert' AS DMLAction,

CG.CityGroup,

C.City

FROM Cities C

INNER JOIN CityGroups CG

ON C.CityRank BETWEEN CG.RankStart AND CG.RankEnd

WHERE

NOT EXISTS

(

SELECT

CGM.City

FROM CityGroupMembers CGM

WHERE

CGM.City = C.City AND

CG.CityGroup = CGM.CityGroup

)

--Delete data-Not really needed when the table is populated for the first time

DELETE CGM

--SELECT 'Delete' AS DMLAction, CityGroup,City

FROM dbo.CityGroupmembers CGM

WHERE

NOTEXISTS

(

SELECT

CG.CityGroup,

C.City

FROM Cities C

INNER JOIN CityGroups CG

ON C.CityRank BETWEEN CG.RankStart AND CG.RankEnd

WHERE

CGM.City = C.City AND

CG.CityGroup = CGM.CityGroup

)

--Confirm the population of CityGroupmembers is correct

SELECT

*

FROM CityGroupmembers

ORDER BY

CityGroup

--Truncate and repopulate the Cities table simulating a data refresh

--View the cities before the refresh if you like

--SELECT * FROM Cities ORDER BY CityRank

TRUNCATE TABLE Cities

INSERT INTO Cities

SELECT 'Montreal',1 UNION

SELECT 'Chicago',2 UNION

SELECT 'Mexico City',3 UNION

SELECT 'San Diego',4 UNION

SELECT 'New York',5 UNION

SELECT 'Brasilia',6 UNION

SELECT 'Paris',7 UNION

SELECT 'Berlin',8 UNION

SELECT 'Tokyo',9 UNION

SELECT 'Hong Kong',10

--View the cities after the refresh if you like

--SELECT * FROM Cities ORDER BY CityRank

--Re-populate CityGroupmembers based on the current data in table Cities

--Insert data

INSERT INTO CityGroupmembers

SELECT --'Insert' AS DMLAction,

CG.CityGroup,

C.City

FROM Cities C

INNER JOIN CityGroups CG

ON C.CityRank BETWEEN CG.RankStart AND CG.RankEnd

WHERE

NOT EXISTS

(

SELECT

CGM.City

FROM CityGroupMembers CGM

WHERE

CGM.City = C.City AND

CG.CityGroup = CGM.CityGroup

)

--Delete data

DELETE CGM

--SELECT 'Delete' AS DMLAction, CityGroup,City

FROM dbo.CityGroupmembers CGM

WHERE

NOTEXISTS

(

SELECT

CG.CityGroup,

C.City

FROM Cities C

INNER JOIN CityGroups CG

ON C.CityRank BETWEEN CG.RankStart AND CG.RankEnd

WHERE

CGM.City = C.City AND

CG.CityGroup = CGM.CityGroup

)

--Confirm the population of CityGroupmembers is correct after the data is refreshed

SELECT

*

FROM CityGroupmembers

ORDER BY

CityGroup