May 20, 2013 at 4:19 pm
Thanks in advance if you can offer any assistance.
I have a table of ranked cities-#Cities
I have a table called #CityGroups with a RankStart and RankEnd field. Example, GroupA should contain the top 3 ranked cities.
I have a table #CityGroupmembers which I periodically need to refresh as the data in table #Cities is updated.
This would be the classic MERGE scenario but table #CityGroupmembers is accessed through a linked server, so I need to generate an insert and a delete statement based on existence checks, that is I can not delete all members of a group and then repopulate in its entirety.
I would like to include ties if there are ties in table #Cities, not shown in my sample data.
I guess the rub (at least for me) is how do I extract the rankings from table #CityGroups and use them in my insert and delete statements.
I would strongly prefer to not use dynamic SQL.
CREATE TABLE #Cities
(
City varchar (20),
CityRank int
)
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
CREATE TABLE #CityGroups
(
CityGroup varchar(10),
CityDescription varchar(15),
RankStart int,
RankEnd int
)
INSERT INTO #CityGroups
SELECT 'GroupA','Top 3 Cities',1,3 UNION
SELECT 'GroupB','Top 10 Cities',1,10 UNION
SELECT 'GroupC','Middle Eight',2,9
CREATE TABLE #CityGroupmembers
(
CityGroup varchar(10),
City varchar (20)
)
--The inserts I would be trying to generate
--These are obviously not getting the rankings from the #CityGroups table in any dynamic fashion
INSERT INTO #CityGroupmembers
SELECT TOP 3
'GroupA',
City
FROM #Cities
ORDER BY CityRank
INSERT INTO #CityGroupmembers
SELECT TOP 10
'GroupB',
City
FROM #Cities
ORDER BY CityRank
INSERT INTO #CityGroupmembers
SELECT
'GroupC',
City
FROM #Cities
WHERE CityRank BETWEEN 2 AND 9
SELECT * FROM #Cities ORDER BY CityRank
SELECT * FROM #CityGroups
SELECT * FROM #CityGroupmembers
DROP TABLE #Cities
DROP TABLE #CityGroups
DROP TABLE #CityGroupmembers
--Updated list for testing
/*
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
*/
May 20, 2013 at 7:00 pm
First, let me say that the idea of a "dynamic ranking" table that defines multiple ranking sets is an intriguing idea, and I may use it in other applications of my own.
My solution below translates your temp tables into CTEs, which is my preference for these testing situations. I will line out how each CTE relates to the tables of your original post:
* CityGroups represents the table with the same name, of ranking group definitions
* Cities represents the original set of cities
* CityGroupMembers_Base represents what the initial CityGroupMembers table would look like after the inserts. Note that it uses a simple join to accomplish the equivalent of all three inserts, and the join would work just as well with any number of grouping definitions in the CityGroups table. In fact it is the simplicity of this join that makes the use of a "dynamic ranking" table appealing to me.
* Cities_Update represents the changed data to be incorporated into the CityGroupMembers table.
* CityGroupMembers_Update represents a view you might use to compare against the existing CityGroupMembers table to determine the rows for deleting and inserting.
* The main query at the end of all the CTEs is a UNION of two queries, one that identifies the rows to be deleted from the existing CityGroupMembers table and one that identifies the rows to be inserted into it.
And here is my solution:
with
CityGroupsas
(SELECT CityGroup = 'GroupA', CityDescription = 'Top 3 Cities',RankStart = 1,RankEnd = 3 UNION
SELECT 'GroupB','Top 10 Cities',1,10 UNION
SELECT 'GroupC','Middle Eight',2,9),
Cities as
(SELECT City = 'Paris', CityRank = 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),
CityGroupMembers_Base as
(select
CityGroups.CityGroup,
Cities.City
from
Cities inner join
CityGroups on Cities.CityRank between RankStart and RankEnd),
Cities_Update as
(SELECT City = 'Montreal', CityRank = 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),
CityGroupMembers_Update as
(select
CityGroups.CityGroup,
Cities_Update.City
from
Cities_Update inner join
CityGroups on Cities_Update.CityRank between RankStart and RankEnd)
select
ChangeType = 'Delete',
CityGroupMembers_Base.City,
CityGroupMembers_Base.CityGroup
from
CityGroupMembers_Base
where
not exists (select City from CityGroupMembers_Update where CityGroupMembers_Base.City = CityGroupMembers_Update.City and CityGroupMembers_Base.CityGroup = CityGroupMembers_Update.CityGroup)
union all
select
ChangeType = 'Insert',
CityGroupMembers_Update.City,
CityGroupMembers_Update.CityGroup
from
CityGroupMembers_Update
where not exists
(select City from CityGroupMembers_Base where CityGroupMembers_Base.City = CityGroupMembers_Update.City and CityGroupMembers_Base.CityGroup = CityGroupMembers_Update.CityGroup)
I'm not sure if this addresses all your issues with having to pull data from a linked server, but hopefully can frame at least part of your solution.
May 22, 2013 at 1:27 pm
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
May 22, 2013 at 2:38 pm
Glad I could help out. Although they are needed rarely, non-equi-joins (joins on an expression other than columnA = columnB) can be just the ticket for complex relational problems.
May 22, 2013 at 2:46 pm
Do be careful when doing "non-equi" joins.
http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply