Created
October 22, 2015 08:50
-
-
Save kyip-dev/d38cd711eae0def4105e to your computer and use it in GitHub Desktop.
SQL-selectInsertMissingRangeRecord
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE IP_TO_LOCATION ( | |
"IP_FROM" NUMBER NOT NULL, | |
"IP_TO" NUMBER NOT NULL, | |
"COUNTRY_CODE" VARCHAR2(3 CHAR) NOT NULL, | |
"COUNTRY_NAME" VARCHAR2(64 CHAR) NOT NULL, | |
"TYPE" VARCHAR2(2 CHAR), | |
"CHANGE_TS" TIMESTAMP (6) DEFAULT systimestamp, | |
CONSTRAINT IP_TO_LOCATION_PK PRIMARY KEY ("IP_FROM", "IP_TO", "TYPE") | |
) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT 'INSERT INTO IP_TO_LOCATION (IP_FROM, IP_TO, COUNTRY_CODE, COUNTRY_NAME, TYPE, CHANGE_TS) VALUES (''' || FROM_IP || ''', ''' || TO_IP || ''', ''GB'', ''DUMMY UK'', ''v4'', SYSTIMESTAMP);' FROM ( | |
SELECT IP_FROM, IP_TO, | |
IP_TO + 1 AS FROM_IP, NVL((SELECT MIN(IP_FROM) - 1 FROM IP_TO_LOCATION t3 WHERE TYPE = 'v4' AND t1.IP_TO < t3.IP_FROM), 4294967295) AS TO_IP | |
FROM IP_TO_LOCATION t1 | |
WHERE TYPE = 'v4' | |
AND NOT EXISTS ( | |
SELECT 1 | |
FROM IP_TO_LOCATION t2 | |
WHERE TYPE = 'v4' | |
AND t2.IP_FROM = t1.IP_TO + 1 | |
) | |
) | |
ORDER BY FROM_IP | |
; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment