Split a postcode range into separate records

1
CREATE FUNCTION [dbo].[fnDSA2ISA_PostcodeBranch]()
2
RETURNS @MyResult TABLE
3
 (PostcodeBranchCode VARCHAR(16),
4
 Postcode INT,
5
 BranchCode VARCHAR(16),
6
 MetaCreatieDatum DATETIME
7
 )
8
AS
9
BEGIN
10

11
DECLARE @TEMP_PostcodeBranch TABLE(
12
 BranchCode VARCHAR(16),
13
 PostcodeStart INT,
14
 PostcodeEind INT);
15

16
WITH BS
17
AS(
18
SELECT ROS.KostenPlaatsCode AS BranchCode
19
 , PostalCodeStart AS PostcodeStart
20
 , PostCodeEnd AS PostcodeEind
21
FROM SourceTable
22

23
)
24

25
INSERT INTO @TEMP_PostcodeBranch (BranchCode, PostcodeStart, PostcodeEind)
26
SELECT * FROM BS;
27

28
WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
29
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
30
 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
31
 Hundreds(N) AS (SELECT 1 FROM Tens t1, Tens t2),
32
 Millions(N) AS (SELECT 1 FROM Hundreds t1, Hundreds t2),
33
 Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM Millions)
34

35
INSERT @MyResult
36
SELECT DISTINCT
37
 CAST(t1.BranchCode + '_' + CAST(t2.n AS VARCHAR(6)) AS VARCHAR(16)) AS PostcodeBranchCode,
38
 t2.N AS Postcode,
39
 t1.BranchCode,
40
 GETDATE() AS MetaCreatieDatum
41
FROM @TEMP_PostcodeBranch t1
42
JOIN Tally t2 ON t2.N BETWEEN t1.PostcodeStart AND t1.PostcodeEind;
43
RETURN;
44

45
END