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