Sample Data (Original Table)
DECLARE @Sample TABLE(IdentityColumn INT,IDcolumn VARCHAR,PRIMARY KEY(IdentityColumn, IDcolumn))
INSERT INTO @Sample SELECT
1, 'a' UNION ALL SELECT
2, 'b' UNION ALL SELECT
4, 'c' UNION ALL SELECT
5, 'd' UNION ALL SELECT
8, 'e'
|
Sample Data (Refill Data Table)
DECLARE @Refill TABLE(IDcolumn VARCHAR PRIMARY KEY)
INSERT INTO @Refill SELECT
'f' UNION ALL SELECT
'g' UNION ALL SELECT
'h' UNION ALL SELECT
'c' UNION ALL SELECT
'd'
|
Query
;WITH TotalRowCount AS(
SELECT
Cnt = COUNT(1)
FROM (SELECT
IDcolumn1
= Sample.IDcolumn,
Refill.IDcolumn
FROM @Sample Sample
FULL JOIN @Refill Refill
ON Sample.IDcolumn = Refill.IDcolumn)Temp
), RebuildRowNumber AS(
SELECT
Cnt = 1
UNION ALL
SELECT
Cnt = RebuildRowNumber.Cnt + 1
FROM RebuildRowNumber
JOIN TotalRowCount
ON RebuildRowNumber.Cnt < TotalRowCount.Cnt
), MissingGap AS(
SELECT
RebuildRowNumber.Cnt,
Refill.IDcolumn
FROM RebuildRowNumber
LEFT JOIN @Sample Sample
ON RebuildRowNumber.Cnt = Sample.IdentityColumn
FULL JOIN @Refill Refill
ON Sample.IDcolumn = Refill.IDcolumn
WHERE Sample.IDcolumn IS NULL
), MakeFullSet AS(
SELECT
MissingGap1.Cnt,
MissingGap2.IDcolumn
FROM MissingGap MissingGap1,
MissingGap MissingGap2
WHERE MissingGap2.IDcolumn IS NOT NULL
AND MissingGap1.Cnt IS NOT NULL
), FindUnique AS(
SELECT
RN = ROW_NUMBER() OVER (PARTITION BY IDcolumn ORDER BY Cnt) - DENSE_RANK()
OVER (ORDER BY IDcolumn),
*
FROM MakeFullSet
)
INSERT INTO @Sample
SELECT
Cnt,
IDcolumn
FROM FindUnique
WHERE RN = 0
SELECT *
FROM @Sample
|
Desired Output
No comments:
Post a Comment