TSQL Lookup using BETWEEN (Ip Addresses)

Question

Post reply

TSQL Lookup using BETWEEN (Ip Addresses)

r5d4

SSCrazy

Points: 2523
More actions
April 12, 2010 at 3:24 am

#223147

I have a lookup table (ipCountryLookup) like this >
startIP endIP countrycode
5033164868257567US
I'm trying to update a list of IPs I have seen (my.IPaddresses) (relevant columns are IPNumber and countrycode)
UPDATE --TOP (5000)
my.IPaddresses
SET countryCode = countrylookup.[countryCode]
FROM my.IPaddresses existingIP WITH (NOLOCK)
INNER JOIN [import].[ipCountryLookup] countrylookup WITH (NOLOCK)
ON existingIP.[IpNumber] BETWEEN countrylookup.[startIpNumber] AND countrylookup.[endIPNumber]
WHERE existingIP.countrycode is NULL OPTION (MAXDOP 1)
Anyone got any ideas to improve on this?
My tables are below. For reference, my IPcountrylookup table has 108726 rows
and I've recorded 22515504 unique IPs so far.
I know I could populate a table with every possible combination of IP and countrycode, but i reckon this will use 40GB disk space (which I don't have)
thanks for your time.
r
=============================================================================
CREATE TABLE [my].[ipAddresses](
[ipNumber] [bigint] NOT NULL,
[ipAddress] [varchar](15) NOT NULL,
[countryCode] [char](2) NULL,
[companyName] [nvarchar](450) NULL,
[clientId] [int] NULL,
[agencyId] [int] NULL,
[isSpider] [bit] NULL,
[cityName] [varchar](50) NULL,
CONSTRAINT [PK_ipAddresses_1] PRIMARY KEY CLUSTERED
(
[ipNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [import].[IpCountryLookup](
[startIpNumber] [bigint] NOT NULL,
[endIpNumber] [bigint] NOT NULL,
[countryCode] [varchar](5) NOT NULL,
CONSTRAINT [PK_IpCountryLookup] PRIMARY KEY CLUSTERED
(
[startIpNumber] ASC,
[endIpNumber] ASC,
[countryCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ixCountryLookup] ON [import].[IpCountryLookup]
(
[startIpNumber] ASC,
[endIpNumber] ASC
)
INCLUDE ( [countryCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Viewing 15 posts - 1 through 15 (of 37 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

You have given create table statements, alrgiht, but what of the sample data and a more clearer requirement mate?

how about you going through this following article and helping us help you?? 🙂

CLICK HERE TO KNOW ABOUT FORUM POSTING ETIQUETTES - JEFF MODEN[/url]

When u do so, i am sure a lot of us will help u instantly...

So please post

1. SAMPLE DATA - INSERT INTO TABLE scripts

2. Desired output - some visual representation of this.

We could guess very little information from your post mate 🙁 !!

Stefan_G SSCertifiable Points: 6609 More actions · Answer 2

r5d4 (4/12/2010)
Anyone got any ideas to improve on this?

In what way do you want to improve it ?

Does it not work correctly ?

Performance ?

Disk space ?

Just asking us to improve something is too vague.

r5d4 SSCrazy Points: 2523 More actions · Answer 3

Hiya,

Thanks for your prompt response.

Point taken about the data and clarity of my question.

My aim is to populate the countycode column of table my.ipaddresses by looking up the ipnumber from the range defined by startipnumber and endipnumber in import.ipcountrylookup.

The sql I am using is

UPDATE

my.IPaddresses

SET countryCode = countrylookup.[countryCode]

FROM my.IPaddresses existingIP WITH (NOLOCK)

INNER JOIN [import].[ipCountryLookup] countrylookup WITH (NOLOCK)

ON existingIP.[IpNumber] BETWEEN countrylookup.[startIpNumber] AND countrylookup.[endIPNumber]

WHERE existingIP.countrycode is NULL OPTION (MAXDOP 1)

I've reproduced a simplified version below.

It's not really representative because i can only give you limited sample data here (1000 rows for each table) and in reality i'm looking to optimise where my.ipaddresses contains 22508934 rows (and growing daily) and import.ipcountrylookup contains 108726 rows (pretty static, changes by a few thousand rows each month).

So to summarise, I'm looking for a way to improve the performance of populating the countrycode column in my.IPaddresses for the full data set.

I'm hoping someone has experience of optimising a range lookup as

a) I havent the disk space for to create a table containing every countrycode/ip combination for an exact match (this takes 40GB).

b) I have the same problem but with City data (much , much larger) to cope with shortly.

thank you for your time

r

code to recreate follows >

--------------------------

CREATE SCHEMA my AUTHORIZATION dbo

GO

CREATE SCHEMA import AUTHORIZATION dbo

GO

CREATE TABLE [my].[ipAddresses](

[ipNumber] [bigint] NOT NULL,

[countryCode] [char](2) NULL,

CONSTRAINT [PK_ipAddresses_1] PRIMARY KEY CLUSTERED

(

[ipNumber] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

INSERT INTO [my].[ipaddresses] (ipnumber)

SELECT '16843009' UNION ALL

SELECT '68300451' UNION ALL

SELECT '68313605' UNION ALL

SELECT '68438915' UNION ALL

SELECT '68444787' UNION ALL

SELECT '68465220' UNION ALL

SELECT '68495710' UNION ALL

SELECT '68502581' UNION ALL

SELECT '68503294' UNION ALL

SELECT '68512806' UNION ALL

SELECT '68516365' UNION ALL

SELECT '68528066' UNION ALL

SELECT '68538854' UNION ALL

SELECT '68568345' UNION ALL

SELECT '68579874' UNION ALL

SELECT '68586952' UNION ALL

SELECT '68637540' UNION ALL

SELECT '68637640' UNION ALL

SELECT '68657154' UNION ALL

SELECT '68673889' UNION ALL

SELECT '68673912' UNION ALL

SELECT '68673930' UNION ALL

SELECT '68674005' UNION ALL

SELECT '68674012' UNION ALL

SELECT '68674100' UNION ALL

SELECT '68674293' UNION ALL

SELECT '68674302' UNION ALL

SELECT '68675384' UNION ALL

SELECT '68675477' UNION ALL

SELECT '68675513' UNION ALL

SELECT '69496094' UNION ALL

SELECT '69518158' UNION ALL

SELECT '69518180' UNION ALL

SELECT '69552018' UNION ALL

SELECT '69553242' UNION ALL

SELECT '69555204' UNION ALL

SELECT '69612774' UNION ALL

SELECT '69943806' UNION ALL

SELECT '70347830' UNION ALL

SELECT '70348314' UNION ALL

SELECT '70584910' UNION ALL

SELECT '70587534' UNION ALL

SELECT '70587554' UNION ALL

SELECT '70587766' UNION ALL

SELECT '70593654' UNION ALL

SELECT '70594082' UNION ALL

SELECT '70601282' UNION ALL

SELECT '70602798' UNION ALL

SELECT '70603334' UNION ALL

SELECT '70604034' UNION ALL

SELECT '70606338' UNION ALL

SELECT '70613154' UNION ALL

SELECT '70614291' UNION ALL

SELECT '70619185' UNION ALL

SELECT '70909954' UNION ALL

SELECT '70910014' UNION ALL

SELECT '70911587' UNION ALL

SELECT '70917298' UNION ALL

SELECT '70922290' UNION ALL

SELECT '70936618' UNION ALL

SELECT '70937826' UNION ALL

SELECT '70937871' UNION ALL

SELECT '70952995' UNION ALL

SELECT '70975746' UNION ALL

SELECT '70979714' UNION ALL

SELECT '70979758' UNION ALL

SELECT '70979818' UNION ALL

SELECT '70980070' UNION ALL

SELECT '70981974' UNION ALL

SELECT '70982402' UNION ALL

SELECT '70983441' UNION ALL

SELECT '70989425' UNION ALL

SELECT '70994973' UNION ALL

SELECT '71013150' UNION ALL

SELECT '71013394' UNION ALL

SELECT '71013517' UNION ALL

SELECT '71013518' UNION ALL

SELECT '71013707' UNION ALL

SELECT '71016450' UNION ALL

SELECT '71020550' UNION ALL

SELECT '71024590' UNION ALL

SELECT '71024674' UNION ALL

SELECT '71024733' UNION ALL

SELECT '71025346' UNION ALL

SELECT '71767090' UNION ALL

SELECT '71767174' UNION ALL

SELECT '71767434' UNION ALL

SELECT '71767438' UNION ALL

SELECT '71767470' UNION ALL

SELECT '71768158' UNION ALL

SELECT '71768195' UNION ALL

SELECT '71786607' UNION ALL

SELECT '71797434' UNION ALL

SELECT '71797874' UNION ALL

SELECT '71803070' UNION ALL

SELECT '71806851' UNION ALL

SELECT '71809005' UNION ALL

SELECT '71812602' UNION ALL

SELECT '71816769' UNION ALL

SELECT '71816770' UNION ALL

SELECT '71816776' UNION ALL

SELECT '71818994' UNION ALL

SELECT '71819022' UNION ALL

SELECT '71819810' UNION ALL

SELECT '71824954' UNION ALL

SELECT '71826277' UNION ALL

SELECT '71827142' UNION ALL

SELECT '72220964' UNION ALL

SELECT '72221234' UNION ALL

SELECT '72221286' UNION ALL

SELECT '72222114' UNION ALL

SELECT '72255684' UNION ALL

SELECT '72259875' UNION ALL

SELECT '72260034' UNION ALL

SELECT '72260035' UNION ALL

SELECT '72260482' UNION ALL

SELECT '72260586' UNION ALL

SELECT '72262154' UNION ALL

SELECT '72265994' UNION ALL

SELECT '72266966' UNION ALL

SELECT '72270114' UNION ALL

SELECT '72273187' UNION ALL

SELECT '72274314' UNION ALL

SELECT '72282294' UNION ALL

SELECT '72282332' UNION ALL

SELECT '72284163' UNION ALL

SELECT '72284419' UNION ALL

SELECT '72286465' UNION ALL

SELECT '72290413' UNION ALL

SELECT '72290414' UNION ALL

SELECT '72290808' UNION ALL

SELECT '72290837' UNION ALL

SELECT '72290838' UNION ALL

SELECT '72290845' UNION ALL

SELECT '72293922' UNION ALL

SELECT '72306053' UNION ALL

SELECT '72306948' UNION ALL

SELECT '72315971' UNION ALL

SELECT '72319166' UNION ALL

SELECT '72322587' UNION ALL

SELECT '72323176' UNION ALL

SELECT '72332931' UNION ALL

SELECT '72333730' UNION ALL

SELECT '72337442' UNION ALL

SELECT '72338018' UNION ALL

SELECT '72338723' UNION ALL

SELECT '72338976' UNION ALL

SELECT '72339538' UNION ALL

SELECT '72344901' UNION ALL

SELECT '72345474' UNION ALL

SELECT '72348868' UNION ALL

SELECT '72349060' UNION ALL

SELECT '72350402' UNION ALL

SELECT '72635188' UNION ALL

SELECT '72635219' UNION ALL

SELECT '72746079' UNION ALL

SELECT '72751029' UNION ALL

SELECT '72760614' UNION ALL

SELECT '72835601' UNION ALL

SELECT '72837016' UNION ALL

SELECT '72878134' UNION ALL

SELECT '72878279' UNION ALL

SELECT '72878638' UNION ALL

SELECT '72878695' UNION ALL

SELECT '72879617' UNION ALL

SELECT '72879808' UNION ALL

SELECT '72879856' UNION ALL

SELECT '72879888' UNION ALL

SELECT '72879946' UNION ALL

SELECT '72882919' UNION ALL

SELECT '72888881' UNION ALL

SELECT '72889255' UNION ALL

SELECT '72889967' UNION ALL

SELECT '72890085' UNION ALL

SELECT '72890096' UNION ALL

SELECT '72890112' UNION ALL

SELECT '72894674' UNION ALL

SELECT '72895242' UNION ALL

SELECT '72896059' UNION ALL

SELECT '72897295' UNION ALL

SELECT '72897820' UNION ALL

SELECT '72898026' UNION ALL

SELECT '72917518' UNION ALL

SELECT '72918591' UNION ALL

SELECT '72918849' UNION ALL

SELECT '73004084' UNION ALL

SELECT '73004860' UNION ALL

SELECT '73005036' UNION ALL

SELECT '73007890' UNION ALL

SELECT '73008244' UNION ALL

SELECT '73008874' UNION ALL

SELECT '73011580' UNION ALL

SELECT '73012878' UNION ALL

SELECT '73021621' UNION ALL

SELECT '73021685' UNION ALL

SELECT '73021937' UNION ALL

SELECT '73097942' UNION ALL

SELECT '73098101' UNION ALL

SELECT '73098109' UNION ALL

SELECT '73098900' UNION ALL

SELECT '73099103' UNION ALL

SELECT '73105495' UNION ALL

SELECT '73106065' UNION ALL

SELECT '73107277' UNION ALL

SELECT '75254554' UNION ALL

SELECT '75580944' UNION ALL

SELECT '75580984' UNION ALL

SELECT '75584735' UNION ALL

SELECT '75585353' UNION ALL

SELECT '75632870' UNION ALL

SELECT '75633069' UNION ALL

SELECT '75634109' UNION ALL

SELECT '75664901' UNION ALL

SELECT '75694794' UNION ALL

SELECT '75694981' UNION ALL

SELECT '75695508' UNION ALL

SELECT '75695649' UNION ALL

SELECT '75698449' UNION ALL

SELECT '75699486' UNION ALL

SELECT '75699689' UNION ALL

SELECT '75705335' UNION ALL

SELECT '75705649' UNION ALL

SELECT '75705952' UNION ALL

SELECT '75706365' UNION ALL

SELECT '75707070' UNION ALL

SELECT '75707178' UNION ALL

SELECT '75707664' UNION ALL

SELECT '75707868' UNION ALL

SELECT '75707922' UNION ALL

SELECT '75708278' UNION ALL

SELECT '75708404' UNION ALL

SELECT '75712761' UNION ALL

SELECT '75713334' UNION ALL

SELECT '75713400' UNION ALL

SELECT '75713835' UNION ALL

SELECT '75714067' UNION ALL

SELECT '75714345' UNION ALL

SELECT '75714503' UNION ALL

SELECT '75714867' UNION ALL

SELECT '75715231' UNION ALL

SELECT '75715242' UNION ALL

SELECT '75716335' UNION ALL

SELECT '75716445' UNION ALL

SELECT '75717036' UNION ALL

SELECT '75717113' UNION ALL

SELECT '75717874' UNION ALL

SELECT '75718024' UNION ALL

SELECT '75721702' UNION ALL

SELECT '75722988' UNION ALL

SELECT '75723588' UNION ALL

SELECT '75724003' UNION ALL

SELECT '75727744' UNION ALL

SELECT '75728122' UNION ALL

SELECT '75728748' UNION ALL

SELECT '75728855' UNION ALL

SELECT '75729632' UNION ALL

SELECT '75729792' UNION ALL

SELECT '75730069' UNION ALL

SELECT '75730699' UNION ALL

SELECT '75730875' UNION ALL

SELECT '75733416' UNION ALL

SELECT '75734618' UNION ALL

SELECT '75747338' UNION ALL

SELECT '75747515' UNION ALL

SELECT '75748395' UNION ALL

SELECT '76064113' UNION ALL

SELECT '76067965' UNION ALL

SELECT '76079428' UNION ALL

SELECT '76089506' UNION ALL

SELECT '76104633' UNION ALL

SELECT '76108926' UNION ALL

SELECT '76153647' UNION ALL

SELECT '76175095' UNION ALL

SELECT '76181181' UNION ALL

SELECT '76248465' UNION ALL

SELECT '76343879' UNION ALL

SELECT '76366939' UNION ALL

SELECT '76367039' UNION ALL

SELECT '76367795' UNION ALL

SELECT '76415188' UNION ALL

SELECT '76418180' UNION ALL

SELECT '76444358' UNION ALL

SELECT '76446697' UNION ALL

SELECT '76468956' UNION ALL

SELECT '76474890' UNION ALL

SELECT '76483528' UNION ALL

SELECT '76486487' UNION ALL

SELECT '76490245' UNION ALL

SELECT '76495838' UNION ALL

SELECT '76499497' UNION ALL

SELECT '76499699' UNION ALL

SELECT '76505078' UNION ALL

SELECT '76525145' UNION ALL

SELECT '76539043' UNION ALL

SELECT '76544045' UNION ALL

SELECT '77072107' UNION ALL

SELECT '77076727' UNION ALL

SELECT '77094191' UNION ALL

SELECT '77096643' UNION ALL

SELECT '77097231' UNION ALL

SELECT '77098950' UNION ALL

SELECT '77100497' UNION ALL

SELECT '77107993' UNION ALL

SELECT '77108034' UNION ALL

SELECT '77116549' UNION ALL

SELECT '77116615' UNION ALL

SELECT '77119503' UNION ALL

SELECT '77119534' UNION ALL

SELECT '77119539' UNION ALL

SELECT '77119598' UNION ALL

SELECT '77122577' UNION ALL

SELECT '77122725' UNION ALL

SELECT '77122793' UNION ALL

SELECT '77123467' UNION ALL

SELECT '77123527' UNION ALL

SELECT '77123578' UNION ALL

SELECT '77125025' UNION ALL

SELECT '77132560' UNION ALL

SELECT '77132668' UNION ALL

SELECT '77132719' UNION ALL

SELECT '77132771' UNION ALL

SELECT '77134950' UNION ALL

SELECT '77135752' UNION ALL

SELECT '77136513' UNION ALL

SELECT '77138010' UNION ALL

SELECT '77138165' UNION ALL

SELECT '77138867' UNION ALL

SELECT '77139484' UNION ALL

SELECT '77149501' UNION ALL

SELECT '77149510' UNION ALL

SELECT '77151067' UNION ALL

SELECT '77152387' UNION ALL

SELECT '77152454' UNION ALL

SELECT '77152716' UNION ALL

SELECT '77153137' UNION ALL

SELECT '77154222' UNION ALL

SELECT '77156516' UNION ALL

SELECT '77157309' UNION ALL

SELECT '77157344' UNION ALL

SELECT '77158106' UNION ALL

SELECT '77158182' UNION ALL

SELECT '77159490' UNION ALL

SELECT '77185318' UNION ALL

SELECT '77187784' UNION ALL

SELECT '77187941' UNION ALL

SELECT '77188739' UNION ALL

SELECT '77189029' UNION ALL

SELECT '77189090' UNION ALL

SELECT '77189432' UNION ALL

SELECT '77191380' UNION ALL

SELECT '77192228' UNION ALL

SELECT '77197819' UNION ALL

SELECT '77199378' UNION ALL

SELECT '77199513' UNION ALL

SELECT '77200196' UNION ALL

SELECT '77200274' UNION ALL

SELECT '77200329' UNION ALL

SELECT '77200446' UNION ALL

SELECT '77200867' UNION ALL

SELECT '77200880' UNION ALL

SELECT '77200966' UNION ALL

SELECT '77201982' UNION ALL

SELECT '77202357' UNION ALL

SELECT '77202499' UNION ALL

SELECT '77202562' UNION ALL

SELECT '77202700' UNION ALL

SELECT '77203174' UNION ALL

SELECT '77208212' UNION ALL

SELECT '77209899' UNION ALL

SELECT '77210299' UNION ALL

SELECT '77210498' UNION ALL

SELECT '77210511' UNION ALL

SELECT '77210790' UNION ALL

SELECT '77211283' UNION ALL

SELECT '77214294' UNION ALL

SELECT '77214376' UNION ALL

SELECT '77214460' UNION ALL

SELECT '77214511' UNION ALL

SELECT '77214821' UNION ALL

SELECT '77215033' UNION ALL

SELECT '77215038' UNION ALL

SELECT '77215215' UNION ALL

SELECT '77215728' UNION ALL

SELECT '77218488' UNION ALL

SELECT '77218690' UNION ALL

SELECT '77221483' UNION ALL

SELECT '77222840' UNION ALL

SELECT '77223127' UNION ALL

SELECT '77224468' UNION ALL

SELECT '77224899' UNION ALL

SELECT '77225062' UNION ALL

SELECT '77229785' UNION ALL

SELECT '77232353' UNION ALL

SELECT '77232483' UNION ALL

SELECT '77232653' UNION ALL

SELECT '77232674' UNION ALL

SELECT '77234225' UNION ALL

SELECT '77234488' UNION ALL

SELECT '77234559' UNION ALL

SELECT '77235380' UNION ALL

SELECT '77235452' UNION ALL

SELECT '77235575' UNION ALL

SELECT '77235626' UNION ALL

SELECT '77235740' UNION ALL

SELECT '77252705' UNION ALL

SELECT '77252755' UNION ALL

SELECT '77253334' UNION ALL

SELECT '77253448' UNION ALL

SELECT '77253550' UNION ALL

SELECT '77254064' UNION ALL

SELECT '77254195' UNION ALL

SELECT '77254410' UNION ALL

SELECT '77254545' UNION ALL

SELECT '77254552' UNION ALL

SELECT '77254621' UNION ALL

SELECT '77260197' UNION ALL

SELECT '77260609' UNION ALL

SELECT '77260926' UNION ALL

SELECT '77261379' UNION ALL

SELECT '77262069' UNION ALL

SELECT '77262979' UNION ALL

SELECT '77263533' UNION ALL

SELECT '77264187' UNION ALL

SELECT '77266079' UNION ALL

SELECT '77266568' UNION ALL

SELECT '77270089' UNION ALL

SELECT '77270098' UNION ALL

SELECT '77270232' UNION ALL

SELECT '77271582' UNION ALL

SELECT '77293218' UNION ALL

SELECT '77293233' UNION ALL

SELECT '77293303' UNION ALL

SELECT '77294624' UNION ALL

SELECT '77295549' UNION ALL

SELECT '77296330' UNION ALL

SELECT '77317945' UNION ALL

SELECT '77331081' UNION ALL

SELECT '77331259' UNION ALL

SELECT '77331341' UNION ALL

SELECT '77331529' UNION ALL

SELECT '77331541' UNION ALL

SELECT '77331692' UNION ALL

SELECT '77331876' UNION ALL

SELECT '77332196' UNION ALL

SELECT '77332276' UNION ALL

SELECT '77333298' UNION ALL

SELECT '77341736' UNION ALL

SELECT '77351156' UNION ALL

SELECT '77353237' UNION ALL

SELECT '77353263' UNION ALL

SELECT '77353328' UNION ALL

SELECT '77353333' UNION ALL

SELECT '77357514' UNION ALL

SELECT '77357888' UNION ALL

SELECT '77373269' UNION ALL

SELECT '77384750' UNION ALL

SELECT '77391725' UNION ALL

SELECT '77391756' UNION ALL

SELECT '77391766' UNION ALL

SELECT '77391850' UNION ALL

SELECT '77396308' UNION ALL

SELECT '77396407' UNION ALL

SELECT '77396436' UNION ALL

SELECT '77396469' UNION ALL

SELECT '77399424' UNION ALL

SELECT '77399432' UNION ALL

SELECT '77399441' UNION ALL

SELECT '77399469' UNION ALL

SELECT '77399510' UNION ALL

SELECT '77404687' UNION ALL

SELECT '77404755' UNION ALL

SELECT '77405447' UNION ALL

SELECT '77405469' UNION ALL

SELECT '77405641' UNION ALL

SELECT '77410976' UNION ALL

SELECT '77411628' UNION ALL

SELECT '77411672' UNION ALL

SELECT '77413345' UNION ALL

SELECT '77414735' UNION ALL

SELECT '77415598' UNION ALL

SELECT '77416973' UNION ALL

SELECT '77420105' UNION ALL

SELECT '77420131' UNION ALL

SELECT '77424856' UNION ALL

SELECT '77426273' UNION ALL

SELECT '77429417' UNION ALL

SELECT '77429455' UNION ALL

SELECT '77429493' UNION ALL

SELECT '77466692' UNION ALL

SELECT '77468973' UNION ALL

SELECT '77475141' UNION ALL

SELECT '77475275' UNION ALL

SELECT '77477411' UNION ALL

SELECT '77477421' UNION ALL

SELECT '77477441' UNION ALL

SELECT '77477523' UNION ALL

SELECT '77477526' UNION ALL

SELECT '77477552' UNION ALL

SELECT '77477557' UNION ALL

SELECT '77477593' UNION ALL

SELECT '77479163' UNION ALL

SELECT '77487574' UNION ALL

SELECT '77489033' UNION ALL

SELECT '77492209' UNION ALL

SELECT '77495232' UNION ALL

SELECT '77500618' UNION ALL

SELECT '77503578' UNION ALL

SELECT '77503584' UNION ALL

SELECT '77503663' UNION ALL

SELECT '77514288' UNION ALL

SELECT '77514350' UNION ALL

SELECT '77514373' UNION ALL

SELECT '77521237' UNION ALL

SELECT '77522696' UNION ALL

SELECT '77522743' UNION ALL

SELECT '77525062' UNION ALL

SELECT '77530395' UNION ALL

SELECT '77530416' UNION ALL

SELECT '77530610' UNION ALL

SELECT '77531972' UNION ALL

SELECT '77532025' UNION ALL

SELECT '77532120' UNION ALL

SELECT '77532130' UNION ALL

SELECT '77534329' UNION ALL

SELECT '77535054' UNION ALL

SELECT '77544283' UNION ALL

SELECT '77544398' UNION ALL

SELECT '77546718' UNION ALL

SELECT '77551127' UNION ALL

SELECT '77554399' UNION ALL

SELECT '77555145' UNION ALL

SELECT '77555841' UNION ALL

SELECT '77556514' UNION ALL

SELECT '77556640' UNION ALL

SELECT '77558254' UNION ALL

SELECT '77560454' UNION ALL

SELECT '77560472' UNION ALL

SELECT '77568096' UNION ALL

SELECT '77568802' UNION ALL

SELECT '77574349' UNION ALL

SELECT '77574659' UNION ALL

SELECT '77574780' UNION ALL

SELECT '77575008' UNION ALL

SELECT '77583278' UNION ALL

SELECT '77584810' UNION ALL

SELECT '77585175' UNION ALL

SELECT '77587048' UNION ALL

SELECT '77587323' UNION ALL

SELECT '77588041' UNION ALL

SELECT '77589507' UNION ALL

SELECT '77589659' UNION ALL

SELECT '77589918' UNION ALL

SELECT '77592125' UNION ALL

SELECT '77610233' UNION ALL

SELECT '77649330' UNION ALL

SELECT '77649382' UNION ALL

SELECT '77713776' UNION ALL

SELECT '77754364' UNION ALL

SELECT '77775861' UNION ALL

SELECT '77784911' UNION ALL

SELECT '77795684' UNION ALL

SELECT '77819560' UNION ALL

SELECT '77859260' UNION ALL

SELECT '77859290' UNION ALL

SELECT '77914450' UNION ALL

SELECT '77919902' UNION ALL

SELECT '77933078' UNION ALL

SELECT '77933108' UNION ALL

SELECT '77955554' UNION ALL

SELECT '77965539' UNION ALL

SELECT '78076331' UNION ALL

SELECT '78090661' UNION ALL

SELECT '78097329' UNION ALL

SELECT '78098026' UNION ALL

SELECT '78215051' UNION ALL

SELECT '78314528' UNION ALL

SELECT '78337055' UNION ALL

SELECT '78342560' UNION ALL

SELECT '78344921' UNION ALL

SELECT '78356364' UNION ALL

SELECT '78356375' UNION ALL

SELECT '78385010' UNION ALL

SELECT '78413510' UNION ALL

SELECT '78414218' UNION ALL

SELECT '78416615' UNION ALL

SELECT '78424141' UNION ALL

SELECT '78424979' UNION ALL

SELECT '78508687' UNION ALL

SELECT '78512363' UNION ALL

SELECT '78512742' UNION ALL

SELECT '78512799' UNION ALL

SELECT '78514397' UNION ALL

SELECT '78515364' UNION ALL

SELECT '78572245' UNION ALL

SELECT '78577681' UNION ALL

SELECT '78590038' UNION ALL

SELECT '78646333' UNION ALL

SELECT '78647990' UNION ALL

SELECT '78648028' UNION ALL

SELECT '78679407' UNION ALL

SELECT '78705183' UNION ALL

SELECT '78705370' UNION ALL

SELECT '78707513' UNION ALL

SELECT '78764613' UNION ALL

SELECT '78779062' UNION ALL

SELECT '78823588' UNION ALL

SELECT '78846464' UNION ALL

SELECT '78846862' UNION ALL

SELECT '78874148' UNION ALL

SELECT '78874331' UNION ALL

SELECT '78888799' UNION ALL

SELECT '78911619' UNION ALL

SELECT '78913824' UNION ALL

SELECT '78927091' UNION ALL

SELECT '78974627' UNION ALL

SELECT '78991114' UNION ALL

SELECT '79005278' UNION ALL

SELECT '79016047' UNION ALL

SELECT '79059572' UNION ALL

SELECT '79077401' UNION ALL

SELECT '79087699' UNION ALL

SELECT '79087850' UNION ALL

SELECT '79088360' UNION ALL

SELECT '79093123' UNION ALL

SELECT '79096957' UNION ALL

SELECT '79100961' UNION ALL

SELECT '79140951' UNION ALL

SELECT '79175817' UNION ALL

SELECT '79175838' UNION ALL

SELECT '79175908' UNION ALL

SELECT '79175930' UNION ALL

SELECT '79176034' UNION ALL

SELECT '79176186' UNION ALL

SELECT '79176194' UNION ALL

SELECT '79176320' UNION ALL

SELECT '79176423' UNION ALL

SELECT '79176489' UNION ALL

SELECT '79176530' UNION ALL

SELECT '79176539' UNION ALL

SELECT '79176722' UNION ALL

SELECT '79176750' UNION ALL

SELECT '79176856' UNION ALL

SELECT '79176863' UNION ALL

SELECT '79177154' UNION ALL

SELECT '79177164' UNION ALL

SELECT '79177196' UNION ALL

SELECT '79177330' UNION ALL

SELECT '79177504' UNION ALL

SELECT '79177548' UNION ALL

SELECT '79177590' UNION ALL

SELECT '79177604' UNION ALL

SELECT '79177683' UNION ALL

SELECT '79177714' UNION ALL

SELECT '79184804' UNION ALL

SELECT '79188612' UNION ALL

SELECT '79224002' UNION ALL

SELECT '79224986' UNION ALL

SELECT '79225095' UNION ALL

SELECT '79225219' UNION ALL

SELECT '79225301' UNION ALL

SELECT '79225356' UNION ALL

SELECT '79225586' UNION ALL

SELECT '79225618' UNION ALL

SELECT '79225649' UNION ALL

SELECT '79225663' UNION ALL

SELECT '79225790' UNION ALL

SELECT '79225838' UNION ALL

SELECT '79226045' UNION ALL

SELECT '79226117' UNION ALL

SELECT '79226350' UNION ALL

SELECT '79226502' UNION ALL

SELECT '79226574' UNION ALL

SELECT '79226682' UNION ALL

SELECT '79226714' UNION ALL

SELECT '79228945' UNION ALL

SELECT '79229417' UNION ALL

SELECT '79229814' UNION ALL

SELECT '79229926' UNION ALL

SELECT '79229973' UNION ALL

SELECT '79229990' UNION ALL

SELECT '79230393' UNION ALL

SELECT '79230491' UNION ALL

SELECT '79230706' UNION ALL

SELECT '79230754' UNION ALL

SELECT '79231107' UNION ALL

SELECT '79279174' UNION ALL

SELECT '79279874' UNION ALL

SELECT '79279878' UNION ALL

SELECT '79279879' UNION ALL

SELECT '79279882' UNION ALL

SELECT '79279890' UNION ALL

SELECT '79279892' UNION ALL

SELECT '79279896' UNION ALL

SELECT '79279905' UNION ALL

SELECT '79279907' UNION ALL

SELECT '79279912' UNION ALL

SELECT '79279913' UNION ALL

SELECT '79279918' UNION ALL

SELECT '79279920' UNION ALL

SELECT '79279925' UNION ALL

SELECT '79279927' UNION ALL

SELECT '79279931' UNION ALL

SELECT '79279935' UNION ALL

SELECT '79279937' UNION ALL

SELECT '79279939' UNION ALL

SELECT '79279940' UNION ALL

SELECT '79279941' UNION ALL

SELECT '79279958' UNION ALL

SELECT '79279959' UNION ALL

SELECT '79279965' UNION ALL

SELECT '79279967' UNION ALL

SELECT '79279973' UNION ALL

SELECT '79279976' UNION ALL

SELECT '79279980' UNION ALL

SELECT '79279984' UNION ALL

SELECT '79279999' UNION ALL

SELECT '79280023' UNION ALL

SELECT '79280035' UNION ALL

SELECT '79280039' UNION ALL

SELECT '79280043' UNION ALL

SELECT '79280052' UNION ALL

SELECT '79280054' UNION ALL

SELECT '79280056' UNION ALL

SELECT '79280062' UNION ALL

SELECT '79280066' UNION ALL

SELECT '79280074' UNION ALL

SELECT '79280081' UNION ALL

SELECT '79280101' UNION ALL

SELECT '79280111' UNION ALL

SELECT '79280119' UNION ALL

SELECT '79280125' UNION ALL

SELECT '79300983' UNION ALL

SELECT '79301881' UNION ALL

SELECT '79437635' UNION ALL

SELECT '79454852' UNION ALL

SELECT '79481257' UNION ALL

SELECT '79481572' UNION ALL

SELECT '79484896' UNION ALL

SELECT '79618867' UNION ALL

SELECT '79646520' UNION ALL

SELECT '81798975' UNION ALL

SELECT '81799681' UNION ALL

SELECT '81805243' UNION ALL

SELECT '81805271' UNION ALL

SELECT '81806007' UNION ALL

SELECT '81806016' UNION ALL

SELECT '81809077' UNION ALL

SELECT '81810634' UNION ALL

SELECT '81811281' UNION ALL

SELECT '81811332' UNION ALL

SELECT '81823628' UNION ALL

SELECT '81823632' UNION ALL

SELECT '81824364' UNION ALL

SELECT '81825896' UNION ALL

SELECT '81825968' UNION ALL

SELECT '81827358' UNION ALL

SELECT '81834312' UNION ALL

SELECT '81834346' UNION ALL

SELECT '81834473' UNION ALL

SELECT '81835164' UNION ALL

SELECT '81836763' UNION ALL

SELECT '81836766' UNION ALL

SELECT '81837438' UNION ALL

SELECT '81837467' UNION ALL

SELECT '81837476' UNION ALL

SELECT '81840615' UNION ALL

SELECT '81841399' UNION ALL

SELECT '81842718' UNION ALL

SELECT '81842748' UNION ALL

SELECT '81842832' UNION ALL

SELECT '81843504' UNION ALL

SELECT '81844247' UNION ALL

SELECT '81845211' UNION ALL

SELECT '81855184' UNION ALL

SELECT '81864163' UNION ALL

SELECT '81864272' UNION ALL

SELECT '81864419' UNION ALL

SELECT '81864513' UNION ALL

SELECT '81877293' UNION ALL

SELECT '81877340' UNION ALL

SELECT '81877610' UNION ALL

SELECT '81877691' UNION ALL

SELECT '81877722' UNION ALL

SELECT '81878519' UNION ALL

SELECT '81879315' UNION ALL

SELECT '81880492' UNION ALL

SELECT '81880698' UNION ALL

SELECT '81882050' UNION ALL

SELECT '81882450' UNION ALL

SELECT '81882825' UNION ALL

SELECT '81885295' UNION ALL

SELECT '81885309' UNION ALL

SELECT '81885507' UNION ALL

SELECT '81885607' UNION ALL

SELECT '81885633' UNION ALL

SELECT '81885647' UNION ALL

SELECT '81885672' UNION ALL

SELECT '81886038' UNION ALL

SELECT '81886516' UNION ALL

SELECT '81886537' UNION ALL

SELECT '81886637' UNION ALL

SELECT '81886756' UNION ALL

SELECT '81887132' UNION ALL

SELECT '81889362' UNION ALL

SELECT '81891444' UNION ALL

SELECT '81892027' UNION ALL

SELECT '81895243' UNION ALL

SELECT '81896039' UNION ALL

SELECT '81896136' UNION ALL

SELECT '81896317' UNION ALL

SELECT '81896795' UNION ALL

SELECT '81897352' UNION ALL

SELECT '81897752' UNION ALL

SELECT '81898229' UNION ALL

SELECT '81898255' UNION ALL

SELECT '81898397' UNION ALL

SELECT '81898465' UNION ALL

SELECT '81898704' UNION ALL

SELECT '81898911' UNION ALL

SELECT '81899320' UNION ALL

SELECT '81906990' UNION ALL

SELECT '81907204' UNION ALL

SELECT '81908442' UNION ALL

SELECT '81908573' UNION ALL

SELECT '81909726' UNION ALL

SELECT '81910219' UNION ALL

SELECT '81916058' UNION ALL

SELECT '81917663' UNION ALL

SELECT '81920032' UNION ALL

SELECT '81922412' UNION ALL

SELECT '81923186' UNION ALL

SELECT '81924776' UNION ALL

SELECT '81925564' UNION ALL

SELECT '81929266' UNION ALL

SELECT '81934836' UNION ALL

SELECT '81935581' UNION ALL

SELECT '81948432' UNION ALL

SELECT '81948660' UNION ALL

SELECT '81952094' UNION ALL

SELECT '81956157' UNION ALL

SELECT '81960930' UNION ALL

SELECT '81963927' UNION ALL

SELECT '81975547' UNION ALL

SELECT '81976119' UNION ALL

SELECT '81976912' UNION ALL

SELECT '81976978' UNION ALL

SELECT '81977078' UNION ALL

SELECT '81977804' UNION ALL

SELECT '81978441' UNION ALL

SELECT '81979175' UNION ALL

SELECT '81979286' UNION ALL

SELECT '81979381' UNION ALL

SELECT '81979919' UNION ALL

SELECT '81980118' UNION ALL

SELECT '81985407' UNION ALL

SELECT '81989138' UNION ALL

SELECT '81989183' UNION ALL

SELECT '81989191' UNION ALL

SELECT '81989358' UNION ALL

SELECT '81989904' UNION ALL

SELECT '81989914' UNION ALL

SELECT '81990046' UNION ALL

SELECT '81990052' UNION ALL

SELECT '82010439' UNION ALL

SELECT '82010536' UNION ALL

SELECT '82011840' UNION ALL

SELECT '82011842' UNION ALL

SELECT '82012929' UNION ALL

SELECT '82013199' UNION ALL

SELECT '82013297' UNION ALL

SELECT '82013886' UNION ALL

SELECT '82015070' UNION ALL

SELECT '82015596' UNION ALL

SELECT '82015597' UNION ALL

SELECT '82015715' UNION ALL

SELECT '82015863' UNION ALL

SELECT '82016151' UNION ALL

SELECT '82021193' UNION ALL

SELECT '82021269' UNION ALL

SELECT '82035111' UNION ALL

SELECT '82035556' UNION ALL

SELECT '82035652' UNION ALL

SELECT '82035831' UNION ALL

SELECT '82035968' UNION ALL

SELECT '82036097' UNION ALL

SELECT '82036422' UNION ALL

SELECT '82036455' UNION ALL

SELECT '82036720' UNION ALL

SELECT '82044938' UNION ALL

SELECT '82045197' UNION ALL

SELECT '82049040' UNION ALL

SELECT '82049947' UNION ALL

SELECT '82050259' UNION ALL

SELECT '82050420' UNION ALL

SELECT '82050577' UNION ALL

SELECT '82050615' UNION ALL

SELECT '82058983' UNION ALL

SELECT '82074281' UNION ALL

SELECT '82078960' UNION ALL

SELECT '82091782' UNION ALL

SELECT '82091840' UNION ALL

SELECT '82091879' UNION ALL

SELECT '82091903' UNION ALL

SELECT '82091951' UNION ALL

SELECT '82091969' UNION ALL

SELECT '82096542' UNION ALL

SELECT '82097359' UNION ALL

SELECT '82098696' UNION ALL

SELECT '82098786' UNION ALL

SELECT '82098857' UNION ALL

SELECT '82098882' UNION ALL

SELECT '82105752' UNION ALL

SELECT '82106531' UNION ALL

SELECT '82111103' UNION ALL

SELECT '82111918' UNION ALL

SELECT '82112689' UNION ALL

SELECT '82113362' UNION ALL

SELECT '82113389' UNION ALL

SELECT '82113401' UNION ALL

SELECT '82113405' UNION ALL

SELECT '82113518' UNION ALL

SELECT '82116456' UNION ALL

SELECT '82116508' UNION ALL

SELECT '82116529' UNION ALL

SELECT '82116559' UNION ALL

SELECT '82120546' UNION ALL

SELECT '82121467' UNION ALL

SELECT '82122208' UNION ALL

SELECT '82122219' UNION ALL

SELECT '82124329' UNION ALL

SELECT '82124531' UNION ALL

SELECT '82126049' UNION ALL

SELECT '82127461' UNION ALL

SELECT '82133714' UNION ALL

SELECT '82137481' UNION ALL

SELECT '82137554' UNION ALL

SELECT '82137597' UNION ALL

SELECT '82141222' UNION ALL

SELECT '82141315' UNION ALL

SELECT '82143631' UNION ALL

SELECT '82145202' UNION ALL

SELECT '82147391' UNION ALL

SELECT '82147440' UNION ALL

SELECT '82147574' UNION ALL

SELECT '82148165' UNION ALL

SELECT '82150585' UNION ALL

SELECT '82150598' UNION ALL

SELECT '82153513' UNION ALL

SELECT '82153611' UNION ALL

SELECT '82159721' UNION ALL

SELECT '82165239' UNION ALL

SELECT '82165958' UNION ALL

SELECT '82168843' UNION ALL

SELECT '82168987' UNION ALL

SELECT '82169741' UNION ALL

SELECT '82172734' UNION ALL

SELECT '82189957' UNION ALL

SELECT '82189975' UNION ALL

SELECT '82190034' UNION ALL

SELECT '82197745' UNION ALL

SELECT '82209936' UNION ALL

SELECT '82211522' UNION ALL

SELECT '82211541' UNION ALL

SELECT '82212157' UNION ALL

SELECT '82212235' UNION ALL

SELECT '82213687' UNION ALL

SELECT '82213759' UNION ALL

SELECT '82213762' UNION ALL

SELECT '82213791' UNION ALL

SELECT '82216176' UNION ALL

SELECT '82222156' UNION ALL

SELECT '82222236' UNION ALL

SELECT '82222305' UNION ALL

SELECT '82233791' UNION ALL

SELECT '82237608' UNION ALL

SELECT '82237646' UNION ALL

SELECT '82237674' UNION ALL

SELECT '82239039' UNION ALL

SELECT '82239799' UNION ALL

SELECT '82239879' UNION ALL

SELECT '82240646' UNION ALL

SELECT '82240662' UNION ALL

SELECT '82242258' UNION ALL

SELECT '82248258' UNION ALL

SELECT '82268427' UNION ALL

SELECT '82270390' UNION ALL

SELECT '82270469' UNION ALL

SELECT '82270800' UNION ALL

SELECT '82271418' UNION ALL

SELECT '82271497' UNION ALL

SELECT '82271562' UNION ALL

SELECT '82272011' UNION ALL

SELECT '82272031' UNION ALL

SELECT '82280996' UNION ALL

SELECT '82281245' UNION ALL

SELECT '82281478' UNION ALL

SELECT '82281987' UNION ALL

SELECT '82284925' UNION ALL

SELECT '82284974' UNION ALL

SELECT '82285606'

GO

CREATE TABLE [import].[IpCountryLookup](

[startIpNumber] [bigint] NOT NULL,

[endIpNumber] [bigint] NOT NULL,

[countryCode] [varchar](5) NOT NULL,

CONSTRAINT [PK_IpCountryLookup] PRIMARY KEY CLUSTERED

(

[startIpNumber] ASC,

[endIpNumber] ASC,

[countryCode] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [ixCountryLookup] ON [import].[IpCountryLookup]

(

[startIpNumber] ASC,

[endIpNumber] ASC

)

INCLUDE ( [countryCode]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

INSERT INTO [import].[IpCountryLookup] (startipnumber, endipnumber, countrycode)

SELECT '50331648','68257567','US' UNION ALL

SELECT '68257568','68257599','CA' UNION ALL

SELECT '68257600','68259583','US' UNION ALL

SELECT '68259584','68259599','CA' UNION ALL

SELECT '68259600','68296775','US' UNION ALL

SELECT '68296776','68296783','MX' UNION ALL

SELECT '68296784','68298887','US' UNION ALL

SELECT '68298888','68298895','CA' UNION ALL

SELECT '68298896','68305407','US' UNION ALL

SELECT '68305408','68305919','MX' UNION ALL

SELECT '68305920','68314143','US' UNION ALL

SELECT '68314144','68314151','CA' UNION ALL

SELECT '68314152','68395663','US' UNION ALL

SELECT '68395664','68395671','CA' UNION ALL

SELECT '68395672','68438287','US' UNION ALL

SELECT '68438288','68438303','CA' UNION ALL

SELECT '68438304','68649143','US' UNION ALL

SELECT '68649144','68649151','CA' UNION ALL

SELECT '68649152','69533951','US' UNION ALL

SELECT '69533952','69534207','CA' UNION ALL

SELECT '69534208','69915111','US' UNION ALL

SELECT '69915112','69915119','CA' UNION ALL

SELECT '69915120','69956103','US' UNION ALL

SELECT '69956104','69956111','BM' UNION ALL

SELECT '69956112','72303007','US' UNION ALL

SELECT '72303008','72303039','CA' UNION ALL

SELECT '72303040','72348895','US' UNION ALL

SELECT '72348896','72348927','CA' UNION ALL

SELECT '72348928','83886079','US' UNION ALL

SELECT '100663296','121195295','US' UNION ALL

SELECT '121195296','121195327','IT' UNION ALL

SELECT '121195328','134693119','US' UNION ALL

SELECT '134693120','134693375','CA' UNION ALL

SELECT '134693376','134730239','US' UNION ALL

SELECT '134730240','134730495','CA' UNION ALL

SELECT '134730496','135192575','US' UNION ALL

SELECT '135192576','135200767','MX' UNION ALL

SELECT '135200768','135603199','US' UNION ALL

SELECT '135603200','135603455','CA' UNION ALL

SELECT '135603456','152305663','US' UNION ALL

SELECT '152305664','152338431','GB' UNION ALL

SELECT '152338432','167772159','US' UNION ALL

SELECT '184549376','201405503','US' UNION ALL

SELECT '201405504','201405511','PR' UNION ALL

SELECT '201405512','201620303','US' UNION ALL

SELECT '201620304','201620311','CA' UNION ALL

SELECT '201620312','201636503','US' UNION ALL

SELECT '201636504','201636511','PR' UNION ALL

SELECT '201636512','201674095','US' UNION ALL

SELECT '201674096','201674111','CA' UNION ALL

SELECT '201674112','201745663','US' UNION ALL

SELECT '201745664','201745671','PR' UNION ALL

SELECT '201745672','201859071','US' UNION ALL

SELECT '201859072','201859087','VI' UNION ALL

SELECT '201859088','201897983','US' UNION ALL

SELECT '201897984','201898239','PR' UNION ALL

SELECT '201898240','202276999','US' UNION ALL

SELECT '202277000','202277015','PR' UNION ALL

SELECT '202277016','202296847','US' UNION ALL

SELECT '202296848','202296855','PR' UNION ALL

SELECT '202296856','202385407','US' UNION ALL

SELECT '202385408','202385919','PR' UNION ALL

SELECT '202385920','202517983','US' UNION ALL

SELECT '202517984','202517991','PR' UNION ALL

SELECT '202517992','202621343','US' UNION ALL

SELECT '202621344','202621351','PR' UNION ALL

SELECT '202621352','202621431','US' UNION ALL

SELECT '202621432','202621439','PR' UNION ALL

SELECT '202621440','202621479','US' UNION ALL

SELECT '202621480','202621487','PR' UNION ALL

SELECT '202621488','202621495','US' UNION ALL

SELECT '202621496','202621503','PR' UNION ALL

SELECT '202621504','202621703','US' UNION ALL

SELECT '202621704','202621711','PR' UNION ALL

SELECT '202621712','202696783','US' UNION ALL

SELECT '202696784','202696791','PR' UNION ALL

SELECT '202696792','202706431','US' UNION ALL

SELECT '202706432','202706943','PR' UNION ALL

SELECT '202706944','202713343','US' UNION ALL

SELECT '202713344','202713351','PR' UNION ALL

SELECT '202713352','202748735','US' UNION ALL

SELECT '202748736','202748743','PR' UNION ALL

SELECT '202748744','202783159','US' UNION ALL

SELECT '202783160','202783167','PR' UNION ALL

SELECT '202783168','202934671','US' UNION ALL

SELECT '202934672','202934687','VI' UNION ALL

SELECT '202934688','202935551','US' UNION ALL

SELECT '202935552','202935807','PR' UNION ALL

SELECT '202935808','202938479','US' UNION ALL

SELECT '202938480','202938495','VI' UNION ALL

SELECT '202938496','202960079','US' UNION ALL

SELECT '202960080','202960095','PR' UNION ALL

SELECT '202960096','202998063','US' UNION ALL

SELECT '202998064','202998071','PR' UNION ALL

SELECT '202998072','203197063','US' UNION ALL

SELECT '203197064','203197071','CA' UNION ALL

SELECT '203197072','203412087','US' UNION ALL

SELECT '203412088','203412095','PR' UNION ALL

SELECT '203412096','203500119','US' UNION ALL

SELECT '203500120','203500127','PR' UNION ALL

SELECT '203500128','203625391','US' UNION ALL

SELECT '203625392','203625399','PR' UNION ALL

SELECT '203625400','203654735','US' UNION ALL

SELECT '203654736','203654751','PR' UNION ALL

SELECT '203654752','203658271','US' UNION ALL

SELECT '203658272','203658287','PR' UNION ALL

SELECT '203658288','203658303','VI' UNION ALL

SELECT '203658304','203658383','PR' UNION ALL

SELECT '203658384','203658399','US' UNION ALL

SELECT '203658400','203658415','PR' UNION ALL

SELECT '203658416','203658423','VI' UNION ALL

SELECT '203658424','203658479','US' UNION ALL

SELECT '203658480','203658751','PR' UNION ALL

SELECT '203658752','203658831','VI' UNION ALL

SELECT '203658832','203658847','US' UNION ALL

SELECT '203658848','203658911','PR' UNION ALL

SELECT '203658912','203658927','VI' UNION ALL

SELECT '203658928','203658951','PR' UNION ALL

SELECT '203658952','203658967','US' UNION ALL

SELECT '203658968','203658991','VI' UNION ALL

SELECT '203658992','203659007','PR' UNION ALL

SELECT '203659008','203659039','US' UNION ALL

SELECT '203659040','203659095','VI' UNION ALL

SELECT '203659096','203659103','US' UNION ALL

SELECT '203659104','203659263','PR' UNION ALL

SELECT '203659264','203696487','US' UNION ALL

SELECT '203696488','203696495','PR' UNION ALL

SELECT '203696496','203747823','US' UNION ALL

SELECT '203747824','203747831','PR' UNION ALL

SELECT '203747832','203799607','US' UNION ALL

SELECT '203799608','203799615','PR' UNION ALL

SELECT '203799616','203805087','US' UNION ALL

SELECT '203805088','203805095','PR' UNION ALL

SELECT '203805096','203850679','US' UNION ALL

SELECT '203850680','203850687','CA' UNION ALL

SELECT '203850688','204046335','US' UNION ALL

SELECT '204046336','204047103','PR' UNION ALL

SELECT '204047104','204047119','VI' UNION ALL

SELECT '204047120','204047231','PR' UNION ALL

SELECT '204047232','204047247','VI' UNION ALL

SELECT '204047248','204047255','US' UNION ALL

SELECT '204047256','204047263','PR' UNION ALL

SELECT '204047264','204047279','US' UNION ALL

SELECT '204047280','204047303','VI' UNION ALL

SELECT '204047304','204047311','PR' UNION ALL

SELECT '204047312','204047335','VI' UNION ALL

SELECT '204047336','204047359','US' UNION ALL

SELECT '204047360','204047375','PR' UNION ALL

SELECT '204047376','204047391','US' UNION ALL

SELECT '204047392','204047423','PR' UNION ALL

SELECT '204047424','204047431','US' UNION ALL

SELECT '204047432','204047455','VI' UNION ALL

SELECT '204047456','204047463','PR' UNION ALL

SELECT '204047464','204047479','VI' UNION ALL

SELECT '204047480','204047503','PR' UNION ALL

SELECT '204047504','204047535','VI' UNION ALL

SELECT '204047536','204047551','PR' UNION ALL

SELECT '204047552','204047559','VI' UNION ALL

SELECT '204047560','204047567','PR' UNION ALL

SELECT '204047568','204047583','VI' UNION ALL

SELECT '204047584','204047999','PR' UNION ALL

SELECT '204048000','204048007','VI' UNION ALL

SELECT '204048008','204048015','US' UNION ALL

SELECT '204048016','204048031','VI' UNION ALL

SELECT '204048032','204048063','PR' UNION ALL

SELECT '204048064','204048127','VI' UNION ALL

SELECT '204048128','204048383','PR' UNION ALL

SELECT '204048384','204089327','US' UNION ALL

SELECT '204089328','204089335','PR' UNION ALL

SELECT '204089336','204126151','US' UNION ALL

SELECT '204126152','204126159','PR' UNION ALL

SELECT '204126160','204152831','US' UNION ALL

SELECT '204152832','204153855','PR' UNION ALL

SELECT '204153856','204409943','US' UNION ALL

SELECT '204409944','204409951','PR' UNION ALL

SELECT '204409952','204443703','US' UNION ALL

SELECT '204443704','204443711','PR' UNION ALL

SELECT '204443712','204443735','US' UNION ALL

SELECT '204443736','204443743','PR' UNION ALL

SELECT '204443744','205500987','US' UNION ALL

SELECT '205500988','205500991','CA' UNION ALL

SELECT '205500992','205813063','US' UNION ALL

SELECT '205813064','205813071','PR' UNION ALL

SELECT '205813072','205953311','US' UNION ALL

SELECT '205953312','205953319','PR' UNION ALL

SELECT '205953320','205953327','US' UNION ALL

SELECT '205953328','205953351','PR' UNION ALL

SELECT '205953352','208290127','US' UNION ALL

SELECT '208290128','208290135','PR' UNION ALL

SELECT '208290136','208472175','US' UNION ALL

SELECT '208472176','208472183','PR' UNION ALL

SELECT '208472184','209799199','US' UNION ALL

SELECT '209799200','209799207','GB' UNION ALL

SELECT '209799208','209845143','US' UNION ALL

SELECT '209845144','209845151','DE' UNION ALL

SELECT '209845152','209854735','US' UNION ALL

SELECT '209854736','209854743','SE' UNION ALL

SELECT '209854744','209867103','US' UNION ALL

SELECT '209867104','209867111','CA' UNION ALL

SELECT '209867112','209868799','US' UNION ALL

SELECT '209868800','209869055','IR' UNION ALL

SELECT '209869056','210784255','US' UNION ALL

SELECT '210784256','210784383','BO' UNION ALL

SELECT '210784384','210784767','US' UNION ALL

SELECT '210784768','210786303','BO' UNION ALL

SELECT '210786304','210970847','US' UNION ALL

SELECT '210970848','210970855','PR' UNION ALL

SELECT '210970856','211051199','US' UNION ALL

SELECT '211051200','211051207','PR' UNION ALL

SELECT '211051208','211126783','US' UNION ALL

SELECT '211126784','211126911','PR' UNION ALL

SELECT '211126912','211129607','US' UNION ALL

SELECT '211129608','211129615','PR' UNION ALL

SELECT '211129616','211313647','US' UNION ALL

SELECT '211313648','211313655','PR' UNION ALL

SELECT '211313656','211363751','US' UNION ALL

SELECT '211363752','211363759','PR' UNION ALL

SELECT '211363760','211368655','US' UNION ALL

SELECT '211368656','211368663','PR' UNION ALL

SELECT '211368664','211403527','US' UNION ALL

SELECT '211403528','211403535','MS' UNION ALL

SELECT '211403536','211410031','US' UNION ALL

SELECT '211410032','211410039','PR' UNION ALL

SELECT '211410040','211410119','US' UNION ALL

SELECT '211410120','211410135','PR' UNION ALL

SELECT '211410136','211536367','US' UNION ALL

SELECT '211536368','211536375','PR' UNION ALL

SELECT '211536376','211595575','US' UNION ALL

SELECT '211595576','211595583','VI' UNION ALL

SELECT '211595584','211595615','US' UNION ALL

SELECT '211595616','211595623','VI' UNION ALL

SELECT '211595624','211595639','PR' UNION ALL

SELECT '211595640','211596815','US' UNION ALL

SELECT '211596816','211596831','VI' UNION ALL

SELECT '211596832','211597055','US' UNION ALL

SELECT '211597056','211597071','VI' UNION ALL

SELECT '211597072','211597375','US' UNION ALL

SELECT '211597376','211597503','VI' UNION ALL

SELECT '211597504','211597727','US' UNION ALL

SELECT '211597728','211597743','PR' UNION ALL

SELECT '211597744','211597759','US' UNION ALL

SELECT '211597760','211597775','PR' UNION ALL

SELECT '211597776','211597791','VI' UNION ALL

SELECT '211597792','211598399','US' UNION ALL

SELECT '211598400','211598463','VI' UNION ALL

SELECT '211598464','211598623','US' UNION ALL

SELECT '211598624','211598631','VI' UNION ALL

SELECT '211598632','211598639','US' UNION ALL

SELECT '211598640','211598719','VI' UNION ALL

SELECT '211598720','211654127','US' UNION ALL

SELECT '211654128','211654135','PR' UNION ALL

SELECT '211654136','211685151','US' UNION ALL

SELECT '211685152','211685159','PR' UNION ALL

SELECT '211685160','211739175','US' UNION ALL

SELECT '211739176','211739183','PR' UNION ALL

SELECT '211739184','211748207','US' UNION ALL

SELECT '211748208','211748223','PR' UNION ALL

SELECT '211748224','211793423','US' UNION ALL

SELECT '211793424','211793431','PR' UNION ALL

SELECT '211793432','211816087','US' UNION ALL

SELECT '211816088','211816095','PR' UNION ALL

SELECT '211816096','211953287','US' UNION ALL

SELECT '211953288','211953295','PR' UNION ALL

SELECT '211953296','212107575','US' UNION ALL

SELECT '212107576','212107583','PR' UNION ALL

SELECT '212107584','212417791','US' UNION ALL

SELECT '212417792','212417799','PR' UNION ALL

SELECT '212417800','212517199','US' UNION ALL

SELECT '212517200','212517215','VI' UNION ALL

SELECT '212517216','212520815','US' UNION ALL

SELECT '212520816','212520831','VI' UNION ALL

SELECT '212520832','212787199','US' UNION ALL

SELECT '212787200','212788223','PR' UNION ALL

SELECT '212788224','212788479','US' UNION ALL

SELECT '212788480','212788607','VI' UNION ALL

SELECT '212788608','212788647','US' UNION ALL

SELECT '212788648','212788663','PR' UNION ALL

SELECT '212788664','212788671','US' UNION ALL

SELECT '212788672','212788799','PR' UNION ALL

SELECT '212788800','212788815','US' UNION ALL

SELECT '212788816','212788823','PR' UNION ALL

SELECT '212788824','212788863','US' UNION ALL

SELECT '212788864','212789007','PR' UNION ALL

SELECT '212789008','212789039','US' UNION ALL

SELECT '212789040','212789055','VI' UNION ALL

SELECT '212789056','212789063','PR' UNION ALL

SELECT '212789064','212789079','VI' UNION ALL

SELECT '212789080','212789087','US' UNION ALL

SELECT '212789088','212789095','VI' UNION ALL

SELECT '212789096','212789111','US' UNION ALL

SELECT '212789112','212789119','PR' UNION ALL

SELECT '212789120','212789127','VI' UNION ALL

SELECT '212789128','212789135','US' UNION ALL

SELECT '212789136','212789143','PR' UNION ALL

SELECT '212789144','212789151','US' UNION ALL

SELECT '212789152','212789159','PR' UNION ALL

SELECT '212789160','212789167','US' UNION ALL

SELECT '212789168','212789191','PR' UNION ALL

SELECT '212789192','212789199','US' UNION ALL

SELECT '212789200','212789215','VI' UNION ALL

SELECT '212789216','212789231','US' UNION ALL

SELECT '212789232','212789247','VI' UNION ALL

SELECT '212789248','212791295','US' UNION ALL

SELECT '212791296','212791423','VI' UNION ALL

SELECT '212791424','212791431','PR' UNION ALL

SELECT '212791432','212791447','VI' UNION ALL

SELECT '212791448','212791455','US' UNION ALL

SELECT '212791456','212791807','PR' UNION ALL

SELECT '212791808','212791815','US' UNION ALL

SELECT '212791816','212791839','VI' UNION ALL

SELECT '212791840','212792191','US' UNION ALL

SELECT '212792192','212792263','PR' UNION ALL

SELECT '212792264','212792271','US' UNION ALL

SELECT '212792272','212792279','PR' UNION ALL

SELECT '212792280','212792287','US' UNION ALL

SELECT '212792288','212792319','PR' UNION ALL

SELECT '212792320','212793087','US' UNION ALL

SELECT '212793088','212793151','VI' UNION ALL

SELECT '212793152','212793199','US' UNION ALL

SELECT '212793200','212793207','VI' UNION ALL

SELECT '212793208','212793215','PR' UNION ALL

SELECT '212793216','212793343','US' UNION ALL

SELECT '212793344','212794367','VI' UNION ALL

SELECT '212794368','212794559','PR' UNION ALL

SELECT '212794560','212794599','US' UNION ALL

SELECT '212794600','212794783','PR' UNION ALL

SELECT '212794784','212794791','VI' UNION ALL

SELECT '212794792','212794799','US' UNION ALL

SELECT '212794800','212794831','VI' UNION ALL

SELECT '212794832','212794879','US' UNION ALL

SELECT '212794880','212794887','VI' UNION ALL

SELECT '212794888','212794911','US' UNION ALL

SELECT '212794912','212794943','VI' UNION ALL

SELECT '212794944','212794959','US' UNION ALL

SELECT '212794960','212794983','VI' UNION ALL

SELECT '212794984','212794991','US' UNION ALL

SELECT '212794992','212795007','VI' UNION ALL

SELECT '212795008','212795135','PR' UNION ALL

SELECT '212795136','212890775','US' UNION ALL

SELECT '212890776','212890783','PR' UNION ALL

SELECT '212890784','212954927','US' UNION ALL

SELECT '212954928','212954935','PR' UNION ALL

SELECT '212954936','212955087','US' UNION ALL

SELECT '212955088','212955095','PR' UNION ALL

SELECT '212955096','212977063','US' UNION ALL

SELECT '212977064','212977071','PR' UNION ALL

SELECT '212977072','213100183','US' UNION ALL

SELECT '213100184','213100191','PR' UNION ALL

SELECT '213100192','213135351','US' UNION ALL

SELECT '213135352','213135359','PR' UNION ALL

SELECT '213135360','213268431','US' UNION ALL

SELECT '213268432','213268439','PR' UNION ALL

SELECT '213268440','213498031','US' UNION ALL

SELECT '213498032','213498039','PR' UNION ALL

SELECT '213498040','213575847','US' UNION ALL

SELECT '213575848','213575855','PR' UNION ALL

SELECT '213575856','213608911','US' UNION ALL

SELECT '213608912','213608919','PR' UNION ALL

SELECT '213608920','213608927','US' UNION ALL

SELECT '213608928','213608951','PR' UNION ALL

SELECT '213608952','213716903','US' UNION ALL

SELECT '213716904','213716911','CA' UNION ALL

SELECT '213716912','213779847','US' UNION ALL

SELECT '213779848','213779855','PR' UNION ALL

SELECT '213779856','213809159','US' UNION ALL

SELECT '213809160','213809167','PR' UNION ALL

SELECT '213809168','213895679','US' UNION ALL

SELECT '213895680','213895743','VI' UNION ALL

SELECT '213895744','213896799','US' UNION ALL

SELECT '213896800','213896831','VI' UNION ALL

SELECT '213896832','213923839','US' UNION ALL

SELECT '213923840','213924863','PR' UNION ALL

SELECT '213924864','213925007','US' UNION ALL

SELECT '213925008','213925023','PR' UNION ALL

SELECT '213925024','213925055','VI' UNION ALL

SELECT '213925056','213925631','US' UNION ALL

SELECT '213925632','213925887','PR' UNION ALL

SELECT '213925888','213936791','US' UNION ALL

SELECT '213936792','213936799','PR' UNION ALL

SELECT '213936800','213936871','US' UNION ALL

SELECT '213936872','213936879','PR' UNION ALL

SELECT '213936880','213936895','US' UNION ALL

SELECT '213936896','213936903','PR' UNION ALL

SELECT '213936904','214184959','US' UNION ALL

SELECT '214184960','214185151','PR' UNION ALL

SELECT '214185152','214185183','VI' UNION ALL

SELECT '214185184','214185199','US' UNION ALL

SELECT '214185200','214185471','PR' UNION ALL

SELECT '214185472','214185983','US' UNION ALL

SELECT '214185984','214187007','PR' UNION ALL

SELECT '214187008','214187775','VI' UNION ALL

SELECT '214187776','214187807','US' UNION ALL

SELECT '214187808','214187815','PR' UNION ALL

SELECT '214187816','214187823','US' UNION ALL

SELECT '214187824','214187831','PR' UNION ALL

SELECT '214187832','214187887','US' UNION ALL

SELECT '214187888','214187895','PR' UNION ALL

SELECT '214187896','214188031','VI' UNION ALL

SELECT '214188032','214237247','US' UNION ALL

SELECT '214237248','214237311','PR' UNION ALL

SELECT '214237312','214249471','US' UNION ALL

SELECT '214249472','214253567','VI' UNION ALL

SELECT '214253568','214448335','US' UNION ALL

SELECT '214448336','214448343','PR' UNION ALL

SELECT '214448344','214697983','US' UNION ALL

SELECT '214697984','214698007','VI' UNION ALL

SELECT '214698008','214698015','PR' UNION ALL

SELECT '214698016','214698023','US' UNION ALL

SELECT '214698024','214698031','PR' UNION ALL

SELECT '214698032','214698095','VI' UNION ALL

SELECT '214698096','214698103','US' UNION ALL

SELECT '214698104','214698135','VI' UNION ALL

SELECT '214698136','214698143','PR' UNION ALL

SELECT '214698144','214698159','VI' UNION ALL

SELECT '214698160','214698167','PR' UNION ALL

SELECT '214698168','214698175','VI' UNION ALL

SELECT '214698176','214698239','PR' UNION ALL

SELECT '214698240','214698319','VI' UNION ALL

SELECT '214698320','214698367','PR' UNION ALL

SELECT '214698368','214698375','VI' UNION ALL

SELECT '214698376','214698383','US' UNION ALL

SELECT '214698384','214698639','PR' UNION ALL

SELECT '214698640','214698655','VI' UNION ALL

SELECT '214698656','214699231','PR' UNION ALL

SELECT '214699232','214699295','VI' UNION ALL

SELECT '214699296','214858655','US' UNION ALL

SELECT '214858656','214858671','NL' UNION ALL

SELECT '214858672','216417663','US' UNION ALL

SELECT '216417664','216417727','PR' UNION ALL

SELECT '216417728','216637639','US' UNION ALL

SELECT '216637640','216637647','PR' UNION ALL

SELECT '216637648','216820479','US' UNION ALL

SELECT '216820480','216820487','PR' UNION ALL

SELECT '216820488','216835615','US' UNION ALL

SELECT '216835616','216835623','PR' UNION ALL

SELECT '216835624','216881191','US' UNION ALL

SELECT '216881192','216881199','PR' UNION ALL

SELECT '216881200','216996407','US' UNION ALL

SELECT '216996408','216996415','PR' UNION ALL

SELECT '216996416','216996543','US' UNION ALL

SELECT '216996544','216996559','PR' UNION ALL

SELECT '216996560','217027999','US' UNION ALL

SELECT '217028000','217028007','PR' UNION ALL

SELECT '217028008','217046775','US' UNION ALL

SELECT '217046776','217046783','PR' UNION ALL

SELECT '217046784','234881023','US' UNION ALL

SELECT '251658240','289011535','US' UNION ALL

SELECT '289011536','289011543','IT' UNION ALL

SELECT '289011544','323243895','US' UNION ALL

SELECT '323243896','323243903','FR' UNION ALL

SELECT '323243904','332132119','US' UNION ALL

SELECT '332132120','332132127','IL' UNION ALL

SELECT '332132128','355993887','US' UNION ALL

SELECT '355993888','355993895','IT' UNION ALL

SELECT '355993896','368674047','US' UNION ALL

SELECT '368674048','368674303','ES' UNION ALL

SELECT '368674304','385875967','US' UNION ALL

SELECT '402653184','405012479','US' UNION ALL

SELECT '405012480','405143551','CA' UNION ALL

SELECT '405143552','405180415','US' UNION ALL

SELECT '405180416','405184511','CA' UNION ALL

SELECT '405184512','405405695','US' UNION ALL

SELECT '405405696','405422079','PR' UNION ALL

SELECT '405422080','405798911','US' UNION ALL

SELECT '405831680','405843967','US' UNION ALL

SELECT '405843968','405848063','CA' UNION ALL

SELECT '405864448','405913599','US' UNION ALL

SELECT '405921792','405929983','CA' UNION ALL

SELECT '405929984','406126591','US' UNION ALL

SELECT '406142976','406147071','US' UNION ALL

SELECT '406159360','406175743','US' UNION ALL

SELECT '406183936','406188031','CA' UNION ALL

SELECT '406192128','406208511','CA' UNION ALL

SELECT '406216704','406241279','US' UNION ALL

SELECT '406241280','406323199','PR' UNION ALL

SELECT '406323200','406388735','US' UNION ALL

SELECT '406388736','406454271','CA' UNION ALL

SELECT '406454272','406847487','US' UNION ALL

SELECT '406847488','407408639','CA' UNION ALL

SELECT '407408640','407633919','US' UNION ALL

SELECT '407633920','408420351','CA' UNION ALL

SELECT '408420352','408502271','US' UNION ALL

SELECT '408502272','408518655','CA' UNION ALL

SELECT '408518656','408535039','US' UNION ALL

SELECT '408535040','408551423','CA' UNION ALL

SELECT '408551424','409337855','US' UNION ALL

SELECT '409337856','409354239','CA' UNION ALL

SELECT '409370624','409731071','US' UNION ALL

SELECT '409731072','409862143','CA' UNION ALL

SELECT '409862144','410124287','US' UNION ALL

SELECT '410124288','410187047','CA' UNION ALL

SELECT '410187048','410187055','US' UNION ALL

SELECT '410187056','410187175','CA' UNION ALL

SELECT '410187176','410187183','US' UNION ALL

SELECT '410187184','410187703','CA' UNION ALL

SELECT '410187704','410187707','US' UNION ALL

SELECT '410187708','410189823','CA' UNION ALL

SELECT '410189824','410648575','US' UNION ALL

SELECT '410648576','410714111','CA' UNION ALL

SELECT '410714112','411164671','US' UNION ALL

SELECT '411164672','411168767','CA' UNION ALL

SELECT '411172864','411303935','US' UNION ALL

SELECT '411303936','411369471','NL' UNION ALL

SELECT '411566080','411639807','US' UNION ALL

SELECT '411639808','411643903','CA' UNION ALL

SELECT '411648000','411664383','CA' UNION ALL

SELECT '411664384','411680767','US' UNION ALL

SELECT '411680768','411688959','CA' UNION ALL

SELECT '411697152','411746303','CA' UNION ALL

SELECT '411746304','411762687','PR' UNION ALL

SELECT '411762688','411770879','CA' UNION ALL

SELECT '411770880','411779071','US' UNION ALL

SELECT '411779072','411828223','PR' UNION ALL

SELECT '411828224','411893759','US' UNION ALL

SELECT '411893760','411959295','CA' UNION ALL

SELECT '411983872','411992063','A2' UNION ALL

SELECT '412024832','412221439','US' UNION ALL

SELECT '412221440','412229631','CA' UNION ALL

SELECT '412286976','412483583','US' UNION ALL

SELECT '412483584','412549119','CA' UNION ALL

SELECT '412549120','412614655','US' UNION ALL

SELECT '412614656','412647423','CL' UNION ALL

SELECT '412647424','412680191','US' UNION ALL

SELECT '412680192','412688383','CA' UNION ALL

SELECT '412696576','412909567','US' UNION ALL

SELECT '412909568','412917759','CA' UNION ALL

SELECT '412942336','412946431','US' UNION ALL

SELECT '413007872','413908991','US' UNION ALL

SELECT '413908992','413925375','PR' UNION ALL

SELECT '413925376','415760383','US' UNION ALL

SELECT '415760384','416022527','CA' UNION ALL

SELECT '416022528','416153599','US' UNION ALL

SELECT '416153600','416161791','BS' UNION ALL

SELECT '416161792','416219135','US' UNION ALL

SELECT '416219136','416251903','CA' UNION ALL

SELECT '416251904','416546815','US' UNION ALL

SELECT '416546816','416579583','CA' UNION ALL

SELECT '416612352','416628735','US' UNION ALL

SELECT '416628736','416636927','CA' UNION ALL

SELECT '416636928','416641023','US' UNION ALL

SELECT '416645120','416743423','US' UNION ALL

SELECT '416743424','416776191','CA' UNION ALL

SELECT '416776192','417202175','US' UNION ALL

SELECT '417202176','417267711','CA' UNION ALL

SELECT '417267712','417366015','US' UNION ALL

SELECT '417366016','417398783','CA' UNION ALL

SELECT '417398784','417464319','US' UNION ALL

SELECT '417464320','417529855','CA' UNION ALL

SELECT '417529856','417538047','PR' UNION ALL

SELECT '417538048','417775615','US' UNION ALL

SELECT '417775616','417796095','CA' UNION ALL

SELECT '417796096','417800191','US' UNION ALL

SELECT '417800192','417808383','BS' UNION ALL

SELECT '417808384','417824767','CA' UNION ALL

SELECT '417824768','417857535','US' UNION ALL

SELECT '417857536','417923071','AR' UNION ALL

SELECT '417923072','418062335','US' UNION ALL

SELECT '418062336','418070527','CA' UNION ALL

SELECT '418070528','418074623','US' UNION ALL

SELECT '418078720','418119679','CA' UNION ALL

SELECT '418119680','418316287','US' UNION ALL

SELECT '418316288','418320383','CA' UNION ALL

SELECT '418324480','418643967','US' UNION ALL

SELECT '418643968','418676735','CA' UNION ALL

SELECT '418676736','418687743','BS' UNION ALL

SELECT '418687744','418687998','DM' UNION ALL

SELECT '418687999','418693119','BS' UNION ALL

SELECT '418693120','418709503','CA' UNION ALL

SELECT '418709504','418766847','US' UNION ALL

SELECT '418766848','418770943','CA' UNION ALL

SELECT '418775040','419430399','US' UNION ALL

SELECT '419430400','436207615','GB' UNION ALL

SELECT '436207616','452984831','US' UNION ALL

SELECT '469762048','540680895','US' UNION ALL

SELECT '540680896','540680959','BE' UNION ALL

SELECT '540680960','540683775','US' UNION ALL

SELECT '540683776','540683783','DE' UNION ALL

SELECT '540683784','540685567','US' UNION ALL

SELECT '540685568','540685631','FR' UNION ALL

SELECT '540685632','540686591','US' UNION ALL

SELECT '540686592','540686599','GB' UNION ALL

SELECT '540686600','540686847','US' UNION ALL

SELECT '540686848','540686863','GB' UNION ALL

SELECT '540686864','540687103','US' UNION ALL

SELECT '540687104','540687111','GB' UNION ALL

SELECT '540687112','540687231','US' UNION ALL

SELECT '540687232','540687359','CA' UNION ALL

SELECT '540687360','540694527','US' UNION ALL

SELECT '540694528','540694591','IT' UNION ALL

SELECT '540694592','540705023','US' UNION ALL

SELECT '540705024','540705031','IE' UNION ALL

SELECT '540705032','540705535','US' UNION ALL

SELECT '540705536','540705599','IE' UNION ALL

SELECT '540705600','540711935','US' UNION ALL

SELECT '540711936','540712447','NL' UNION ALL

SELECT '540712448','540737535','US' UNION ALL

SELECT '540737536','540737791','BZ' UNION ALL

SELECT '540737792','540737919','BR' UNION ALL

SELECT '540737920','540750175','BZ' UNION ALL

SELECT '540750176','540750207','MX' UNION ALL

SELECT '540750208','540750239','BZ' UNION ALL

SELECT '540750240','540750255','MX' UNION ALL

SELECT '540750256','540803071','BZ' UNION ALL

SELECT '540803072','540811791','US' UNION ALL

SELECT '540811792','540811799','JP' UNION ALL

SELECT '540811800','540811871','US' UNION ALL

SELECT '540811872','540811903','GB' UNION ALL

SELECT '540811904','540811935','US' UNION ALL

SELECT '540811936','540811967','DE' UNION ALL

SELECT '540811968','540811983','JP' UNION ALL

SELECT '540811984','540811991','US' UNION ALL

SELECT '540811992','540811999','DE' UNION ALL

SELECT '540812000','540812295','US' UNION ALL

SELECT '540812296','540812303','SG' UNION ALL

SELECT '540812304','540812319','DE' UNION ALL

SELECT '540812320','540812503','US' UNION ALL

SELECT '540812504','540812511','SG' UNION ALL

SELECT '540812512','540812719','US' UNION ALL

SELECT '540812720','540812727','SG' UNION ALL

SELECT '540812728','540819455','US' UNION ALL

SELECT '540819456','540823551','CA' UNION ALL

SELECT '540823552','540826671','US' UNION ALL

SELECT '540826672','540826719','CA' UNION ALL

SELECT '540826720','540827135','US' UNION ALL

SELECT '540827136','540827263','CA' UNION ALL

SELECT '540827264','540827295','US' UNION ALL

SELECT '540827296','540827311','CA' UNION ALL

SELECT '540827312','540827359','US' UNION ALL

SELECT '540827360','540827375','CA' UNION ALL

SELECT '540827376','540827391','US' UNION ALL

SELECT '540827392','540827423','CA' UNION ALL

SELECT '540827424','540827471','US' UNION ALL

SELECT '540827472','540827487','CA' UNION ALL

SELECT '540827488','540827519','US' UNION ALL

SELECT '540827520','540827551','CA' UNION ALL

SELECT '540827552','540827583','US' UNION ALL

SELECT '540827584','540827647','CA' UNION ALL

SELECT '540827648','540829695','US' UNION ALL

SELECT '540829696','540829951','CA' UNION ALL

SELECT '540829952','540830511','US' UNION ALL

SELECT '540830512','540830527','CA' UNION ALL

SELECT '540830528','540830559','US' UNION ALL

SELECT '540830560','540830575','CA' UNION ALL

SELECT '540830576','540830623','US' UNION ALL

SELECT '540830624','540830735','CA' UNION ALL

SELECT '540830736','540830815','US' UNION ALL

SELECT '540830816','540830831','CA' UNION ALL

SELECT '540830832','540830847','US' UNION ALL

SELECT '540830848','540830895','CA' UNION ALL

SELECT '540830896','543690751','US' UNION ALL

SELECT '543690752','543691007','AR' UNION ALL

SELECT '543691008','543844351','US' UNION ALL

SELECT '543844352','543844607','CH' UNION ALL

SELECT '543844608','603979775','US' UNION ALL

SELECT '637534208','654311423','US' UNION ALL

SELECT '671088640','687865855','US' UNION ALL

SELECT '687865856','689963007','ZA' UNION ALL

SELECT '692830208','692834303','NG' UNION ALL

SELECT '692834304','692838399','TZ' UNION ALL

SELECT '692838400','692842495','ZA' UNION ALL

SELECT '692842496','692846591','ZM' UNION ALL

SELECT '692969472','692971519','TZ' UNION ALL

SELECT '692971520','692973567','ZA' UNION ALL

SELECT '692973568','692975615','MZ' UNION ALL

SELECT '692975616','692977663','EG' UNION ALL

SELECT '692977664','692978687','TZ' UNION ALL

SELECT '692978688','692979711','ZA' UNION ALL

SELECT '693101568','693102591','KE' UNION ALL

SELECT '693102592','693103615','CD' UNION ALL

SELECT '693103616','693104639','GN' UNION ALL

SELECT '693104640','693105663','ZA' UNION ALL

SELECT '693105664','693106687','MZ' UNION ALL

SELECT '693106688','693107711','TZ' UNION ALL

SELECT '693107712','693108735','KE' UNION ALL

SELECT '694157312','695205887','DZ' UNION ALL

SELECT '695205888','696254463','ZA' UNION ALL

SELECT '696254464','696516607','EG' UNION ALL

SELECT '696778752','696844287','MU' UNION ALL

SELECT '696844288','696909823','MA' UNION ALL

SELECT '696909824','696918015','UG' UNION ALL

SELECT '696918016','696926207','NE' UNION ALL

SELECT '696926208','696928255','ZA' UNION ALL

SELECT '696928256','696930303','BW' UNION ALL

SELECT '696930304','696932351','RW' UNION ALL

SELECT '696932352','696933375','BJ' UNION ALL

SELECT '696933376','696934399','TZ' UNION ALL

SELECT '696934400','696942591','BF' UNION ALL

SELECT '696942592','696950783','MR' UNION ALL

SELECT '696950784','696958975','NG' UNION ALL

SELECT '696958976','696963071','TZ' UNION ALL

SELECT '696963072','696967167','UG' UNION ALL

SELECT '696967168','696971263','MZ' UNION ALL

SELECT '696971264','696975359','KE' UNION ALL

SELECT '696975360','696991743','GH' UNION ALL

SELECT '696991744','697008127','NG' UNION ALL

SELECT '697008128','697040895','KE' UNION ALL

SELECT '697040896','697303039','MA' UNION ALL

SELECT '697303040','697827327','ZA' UNION ALL

SELECT '697827328','697958399','EG' UNION ALL

SELECT '697958400','698023935','ZA' UNION ALL

SELECT '698023936','698056703','NG' UNION ALL

SELECT '698056704','698089471','EG' UNION ALL

SELECT '698089472','698220543','ZA' UNION ALL

SELECT '698220544','698351615','GA' UNION ALL

SELECT '698351616','699400191','ZA' UNION ALL

SELECT '699400192','699465727','EG' UNION ALL

SELECT '699465728','699531263','ZA' UNION ALL

SELECT '699531264','699662335','EG' UNION ALL

SELECT '699662336','699793407','ZA' UNION ALL

SELECT '699793408','699858943','NA' UNION ALL

SELECT '699858944','699924479','ZA' UNION ALL

SELECT '699924480','699990015','NG' UNION ALL

SELECT '699990016','700055551','ZA' UNION ALL

SELECT '700055552','700121087','RW' UNION ALL

SELECT '700121088','700186623','EG' UNION ALL

SELECT '700186624','700203007','MG' UNION ALL

SELECT '700203008','700219391','MR' UNION ALL

SELECT '700219392','700235775','TZ' UNION ALL

SELECT '700235776','700252159','ZA' UNION ALL

SELECT '700252160','700260351','NG' UNION ALL

SELECT '700260352','700268543','CI' UNION ALL

SELECT '700268544','700276735','ZA' UNION ALL

SELECT '700276736','700284927','CI' UNION ALL

SELECT '700284928','700293119','GH' UNION ALL

SELECT '700293120','700301311','EG' UNION ALL

SELECT '700301312','700309503','CD' UNION ALL

SELECT '700309504','700317695','DJ' UNION ALL

SELECT '700317696','700325887','NG' UNION ALL

SELECT '700325888','700334079','ZW' UNION ALL

SELECT '700334080','700335103','BJ' UNION ALL

SELECT '700335104','700336127','GH' UNION ALL

SELECT '700336128','700337151','CG' UNION ALL

SELECT '700338176','700339199','CD' UNION ALL

SELECT '700339200','700340223','NA' UNION ALL

SELECT '700340224','700341247','GH' UNION ALL

SELECT '700341248','700342271','MW' UNION ALL

SELECT '700342272','700350463','NA' UNION ALL

SELECT '700350464','700358655','UG' UNION ALL

SELECT '700358656','700366847','MZ' UNION ALL

SELECT '700366848','700375039','MW' UNION ALL

SELECT '700375040','700376063','CM' UNION ALL

SELECT '700376064','700377087','NE' UNION ALL

SELECT '700377088','700378111','KE' UNION ALL

SELECT '700378112','700379135','MG' UNION ALL

SELECT '700379136','700380159','NG' UNION ALL

SELECT '700380160','700381183','BW' UNION ALL

SELECT '700381184','700382207','EG' UNION ALL

SELECT '700382208','700383231','KE' UNION ALL

SELECT '700383232','700399615','EG' UNION ALL

SELECT '700399616','700400639','AO' UNION ALL

SELECT '700400640','700401663','CI' UNION ALL

SELECT '700401664','700402687','MZ' UNION ALL

SELECT '700402688','700403711','UG' UNION ALL

SELECT '700403712','700404735','EG' UNION ALL

SELECT '700404736','700405759','BJ' UNION ALL

SELECT '700405760','700406783','ZA' UNION ALL

SELECT '700406784','700407807','TZ' UNION ALL

SELECT '700407808','700408831','GH' UNION ALL

SELECT '700408832','700409855','CM' UNION ALL

SELECT '700409856','700410879','LR' UNION ALL

SELECT '700410880','700411903','NG' UNION ALL

SELECT '700411904','700412927','KE' UNION ALL

SELECT '700412928','700413951','ZM' UNION ALL

SELECT '700413952','700414975','SC' UNION ALL

SELECT '700414976','700415999','MU' UNION ALL

SELECT '700416000','700432383','ZA' UNION ALL

SELECT '700432384','700434431','KE' UNION ALL

SELECT '700434432','700436479','LS' UNION ALL

SELECT '700436480','700438527','UG' UNION ALL

SELECT '700438528','700442623','KE' UNION ALL

SELECT '700442624','700444671','ZW' UNION ALL

SELECT '700444672','700446719','GH' UNION ALL

SELECT '700446720','700447743','SL' UNION ALL

SELECT '700447744','700448767','DZ' UNION ALL

SELECT '700448768','700579839','ZA' UNION ALL

SELECT '700579840','700580863','SC' UNION ALL

SELECT '700580864','700585727','A2' UNION ALL

SELECT '700585728','700586503','TZ' UNION ALL

SELECT '700586504','700588031','A2' UNION ALL

SELECT '700588032','700588286','KM' UNION ALL

SELECT '700588287','700589567','A2' UNION ALL

SELECT '700589568','700589695','TZ' UNION ALL

SELECT '700589696','700592383','A2' UNION ALL

SELECT '700592384','700592639','KE' UNION ALL

SELECT '700592640','700593151','A2' UNION ALL

SELECT '700593152','700594175','NG' UNION ALL

SELECT '700594176','700645375','A2' UNION ALL

SELECT '700645376','700710911','ZA' UNION ALL

SELECT '700710912','700776447','EG' UNION ALL

SELECT '700776448','700841983','RW' UNION ALL

SELECT '700841984','700895231','ZA' UNION ALL

SELECT '700895232','700899327','NG' UNION ALL

SELECT '700899328','700907519','ZA' UNION ALL

SELECT '700907520','700973055','EG' UNION ALL

SELECT '700973056','701104127','DZ' UNION ALL

SELECT '701104128','701112319','GH' UNION ALL

SELECT '701112320','701120511','ZA' UNION ALL

SELECT '701120512','701145087','CI' UNION ALL

SELECT '701145088','701153279','SD' UNION ALL

SELECT '701153280','701161471','CM' UNION ALL

SELECT '701161472','701169663','UG' UNION ALL

SELECT '701169664','701186047','ZA' UNION ALL

SELECT '701186048','701202431','NG' UNION ALL

SELECT '701202432','701210623','NE' UNION ALL

SELECT '701210624','701214719','ZA' UNION ALL

SELECT '701214720','701218815','LS' UNION ALL

SELECT '701218816','701222911','ML' UNION ALL

SELECT '701222912','701227007','KE' UNION ALL

SELECT '701227008','701231103','BF' UNION ALL

SELECT '701231104','701235199','SC' UNION ALL

SELECT '701235200','701243391','SZ' UNION ALL

SELECT '701243392','701251583','GH' UNION ALL

SELECT '701251584','701259775','CM' UNION ALL

SELECT '701259776','701267967','MG' UNION ALL

SELECT '701267968','701276159','TZ' UNION ALL

SELECT '701276160','701284351','KE' UNION ALL

SELECT '701284352','701292543','ZA' UNION ALL

SELECT '701292544','701300735','NG' UNION ALL

SELECT '701300736','701308927','CM' UNION ALL

SELECT '701308928','701317119','AO' UNION ALL

SELECT '701317120','701325311','CM' UNION ALL

SELECT '701325312','701333503','EG' UNION ALL

SELECT '701333504','701341695','NA' UNION ALL

SELECT '701341696','701349887','NG' UNION ALL

SELECT '701349888','701358079','MA' UNION ALL

SELECT '701358080','701366271','SL' UNION ALL

SELECT '701366272','701374463','NG' UNION ALL

SELECT '701374464','701382655','KE' UNION ALL

SELECT '701382656','701390847','CI' UNION ALL

SELECT '701390848','701399039','GA' UNION ALL

SELECT '701399040','701407231','EG' UNION ALL

SELECT '701407232','701415423','CA' UNION ALL

SELECT '701415424','701423615','ZA' UNION ALL

SELECT '701423616','701431807','NG' UNION ALL

SELECT '701431808','701439999','CI' UNION ALL

SELECT '701440000','701448191','MG' UNION ALL

SELECT '701448192','701464575','KE' UNION ALL

SELECT '701464576','701472767','MU' UNION ALL

SELECT '701472768','701480959','TG' UNION ALL

SELECT '701480960','701489151','CI' UNION ALL

SELECT '701489152','701513727','ZA' UNION ALL

SELECT '701513728','701530111','LY' UNION ALL

SELECT '701530112','701546495','SN' UNION ALL

SELECT '701546496','701562879','ZA' UNION ALL

SELECT '701562880','701579263','KE' UNION ALL

SELECT '701579264','701595647','SD' UNION ALL

SELECT '701595648','701612031','DZ' UNION ALL

SELECT '701612032','701628415','EG' UNION ALL

SELECT '701628416','701644799','GH' UNION ALL

SELECT '701644800','701661183','DZ' UNION ALL

SELECT '701661184','701677567','UG' UNION ALL

SELECT '701677568','701693951','AO' UNION ALL

SELECT '701693952','701702143','GH' UNION ALL

SELECT '701702144','701710335','SZ' UNION ALL

SELECT '701710336','701718527','MU' UNION ALL

SELECT '701718528','701726719','CM' UNION ALL

SELECT '701726720','701743103','GA' UNION ALL

SELECT '701743104','701759487','NG' UNION ALL

SELECT '701759488','701792255','KE' UNION ALL

SELECT '701792256','701825023','MU' UNION ALL

SELECT '701825024','701857791','ZA' UNION ALL

SELECT '701857792','701890559','RE' UNION ALL

SELECT '701890560','701923327','SN' UNION ALL

SELECT '701923328','701956095','MA' UNION ALL

SELECT '701956096','701992959','KE' UNION ALL

SELECT '701992960','701997055','SZ' UNION ALL

SELECT '701997056','702001151','GH' UNION ALL

SELECT '702001152','702005247','ZM' UNION ALL

SELECT '702005248','702009343','KE' UNION ALL

SELECT '702009344','702013439','CV' UNION ALL

SELECT '702013440','702015487','KE' UNION ALL

SELECT '702015488','702017535','ZA' UNION ALL

SELECT '702017536','702018559','EG' UNION ALL

SELECT '702018560','702019583','NG' UNION ALL

SELECT '702019584','702020607','RW' UNION ALL

SELECT '702020608','702021631','CD' UNION ALL

SELECT '702021632','702029823','ZM' UNION ALL

SELECT '702029824','702038015','BJ' UNION ALL

SELECT '702038016','702046207','ZM' UNION ALL

SELECT '702046208','702054399','RW' UNION ALL

SELECT '702054400','702058495','ZA' UNION ALL

SELECT '702058496','702062591','BF' UNION ALL

SELECT '702062592','702066687','NG' UNION ALL

SELECT '702066688','702070783','CM' UNION ALL

SELECT '702070784','702074879','ZA' UNION ALL

SELECT '702074880','702076927','BW' UNION ALL

SELECT '702076928','702078975','TZ' UNION ALL

SELECT '702078976','702079999','MA' UNION ALL

SELECT '702080000','702081023','MW' UNION ALL

SELECT '702081024','702082047','NG' UNION ALL

SELECT '702082048','702083071','AO' UNION ALL

SELECT '702083072','702087167','CI' UNION ALL

SELECT '702087168','702119935','NG' UNION ALL

SELECT '702119936','702128127','CM' UNION ALL

SELECT '702128128','702136319','EG' UNION ALL

SELECT '702136320','702137343','ZA' UNION ALL

SELECT '702137344','702138367','MG' UNION ALL

SELECT '702138368','702139391','TZ' UNION ALL

SELECT '702139392','702140415','NG' UNION ALL

SELECT '702140416','702141439','MZ' UNION ALL

SELECT '702141440','702142463','MU' UNION ALL

SELECT '702142464','702143487','MW' UNION ALL

SELECT '702143488','702144511','KE' UNION ALL

SELECT '702144512','702146559','EG' UNION ALL

SELECT '702146560','702148607','UG' UNION ALL

SELECT '702148608','702150655','ZA' UNION ALL

SELECT '702150656','702152703','ZM' UNION ALL

SELECT '702152704','702169087','SD' UNION ALL

SELECT '702169088','702185471','NA' UNION ALL

SELECT '702185472','702201855','EG' UNION ALL

SELECT '702201856','702218239','GH' UNION ALL

SELECT '702218240','702234623','SN' UNION ALL

SELECT '702234624','702251007','NA' UNION ALL

SELECT '702251008','702283775','NG' UNION ALL

SELECT '702283776','702287871','UG' UNION ALL

SELECT '702287872','702291967','ZW' UNION ALL

SELECT '702291968','702296063','MZ' UNION ALL

SELECT '702296064','702300159','GH' UNION ALL

SELECT '702300160','702304255','NG' UNION ALL

SELECT '702304256','702308351','SL' UNION ALL

SELECT '702308352','702312447','SC' UNION ALL

SELECT '702312448','702316543','KE' UNION ALL

SELECT '702316544','702320639','TZ' UNION ALL

SELECT '702320640','702324735','DZ' UNION ALL

SELECT '702324736','702328831','MZ' UNION ALL

SELECT '702328832','702332927','TZ' UNION ALL

SELECT '702332928','702337023','MZ' UNION ALL

SELECT '702337024','702341119','UG' UNION ALL

SELECT '702341120','702345215','KE' UNION ALL

SELECT '702345216','702349311','TZ' UNION ALL

SELECT '702349312','702353407','ZA' UNION ALL

SELECT '702353408','702357503','DZ' UNION ALL

SELECT '702357504','702365695','TZ' UNION ALL

SELECT '702365696','702369791','MZ' UNION ALL

SELECT '702369792','702373887','UG' UNION ALL

SELECT '702373888','702377983','MW' UNION ALL

SELECT '702377984','702382079','NG' UNION ALL

SELECT '702382080','702386175','EG' UNION ALL

SELECT '702386176','702390271','ZW' UNION ALL

SELECT '702390272','702394367','NG' UNION ALL

SELECT '702394368','702398463','ML' UNION ALL

SELECT '702398464','702402559','CV' UNION ALL

SELECT '702402560','702406655','GH' UNION ALL

SELECT '702406656','702410751','ZA' UNION ALL

SELECT '702410752','702414847','AO' UNION ALL

SELECT '702414848','702416895','UG' UNION ALL

SELECT '702416896','702418943','KE' UNION ALL

SELECT '702418944','702420991','ZM' UNION ALL

SELECT '702420992','702423039','TZ' UNION ALL

SELECT '702423040','702425087','ZA' UNION ALL

SELECT '702425088','702427135','NG' UNION ALL

SELECT '702427136','702429183','ZA' UNION ALL

SELECT '702429184','702431231','TZ' UNION ALL

SELECT '702431232','702435327','NG' UNION ALL

SELECT '702435328','702437375','MZ' UNION ALL

SELECT '702437376','702439423','TZ' UNION ALL

SELECT '702439424','702441471','MU' UNION ALL

SELECT '702441472','702443519','ZA' UNION ALL

SELECT '702443520','702445567','GQ' UNION ALL

SELECT '702445568','702447615','KE' UNION ALL

SELECT '702447616','702449663','EG' UNION ALL

SELECT '702449664','702451711','ZA' UNION ALL

SELECT '702451712','702453759','NG' UNION ALL

SELECT '702453760','702455807','TZ' UNION ALL

SELECT '702455808','702457855','KE' UNION ALL

SELECT '702457856','702459903','EG' UNION ALL

SELECT '702459904','702461951','TZ' UNION ALL

SELECT '702461952','702463999','MW' UNION ALL

SELECT '702464000','702465023','BJ' UNION ALL

SELECT '702465024','702466047','CD' UNION ALL

SELECT '702467072','702468095','SC' UNION ALL

SELECT '702468096','702469119','NG' UNION ALL

SELECT '702469120','702470143','ZA' UNION ALL

SELECT '702470144','702471167','CD' UNION ALL

SELECT '702471168','702474239','ZA' UNION ALL

SELECT '702474240','702475263','GH' UNION ALL

SELECT '702475264','702476287','AO' UNION ALL

SELECT '702476288','702477311','ZM' UNION ALL

SELECT '702477312','702478335','RW' UNION ALL

SELECT '702478336','702481407','AO' UNION ALL

SELECT '702481408','702482431','TZ' UNION ALL

SELECT '702482432','702483455','ZA' UNION ALL

SELECT '702483456','702484479','BW' UNION ALL

SELECT '702484480','702485503','TZ' UNION ALL

SELECT '702485504','702486527','EG' UNION ALL

SELECT '702486528','702487551','NG' UNION ALL

SELECT '702487552','702488575','CM' UNION ALL

SELECT '702488576','702490623','ZA' UNION ALL

SELECT '702490624','702491647','AO' UNION ALL

SELECT '702491648','702492671','LR' UNION ALL

SELECT '702492672','702493695','KE' UNION ALL

SELECT '702493696','702494719','EG' UNION ALL

SELECT '702494720','702495743','KE' UNION ALL

SELECT '702495744','702496767','ZA' UNION ALL

SELECT '702496768','702497791','NG' UNION ALL

SELECT '702497792','702498815','ZA' UNION ALL

SELECT '702498816','702499839','BW' UNION ALL

SELECT '702499840','702500863','MU' UNION ALL

SELECT '702500864','702501887','NA' UNION ALL

SELECT '702501888','702502911','UG'

r5d4 SSCrazy Points: 2523 More actions · Answer 4

just seen the other reply, yes it is performance i'm trying to achieve.

when you have a full data set, lookups using BETWEEN crawl to a halt.

thanks

r

Stefan_G SSCertifiable Points: 6609 More actions · Answer 5

Some random tips:

1) Add a nonclustered index on CountryCode, IpNumber on [my].[ipAddresses]

I assume that when you run this update most of the rows in the table already have a CountryCode. With this index only rows with an unknown country code will be processed, without the index all rows in the table must be scanned.

2) Remove the nonclustered index on startIpNumber, endIpNumber - you already have the same index in the primary key.

3) If you run your update and find an ip with no matching entry in IpCountryLookup you should set CountryCode to a non-null but invalid value (such as 'XX') to mark this row as having an unknown country code. There is a difference between unknown code (XX) and unprocessed row (NULL). This change will mean that only new ip-numbers will need the lookup - not numbers where you have already tried a lookup and failed.

4) Change the datatype of CountryCode to be char(2) in both tables

Stefan_G SSCertifiable Points: 6609 More actions · Answer 6

If the performance is still not good enough you could preprocess the IpCountryLookup to split all blocks that are larger than 64K into several smaller blocks.

You could then rewrite the query like this:

UPDATE my.IPaddresses

SET countryCode = isnull((

SELECT countrylookup.[countryCode]

FROM [import].[ipCountryLookup] countrylookup

WHERE

countrylookup.[startIpNumber] <= my.IPaddresses.ipNumber

AND

countrylookup.[startIpNumber] > my.ipAddresses.ipNumber-65536

AND

countrylookup.[endIpNumber] >= my.ipAddresses.ipNumber

),'XX')

WHERE countrycode IS NULL

This should enable the SQL server to use the index on startIpNumber more efficiently.

The blocksize can be tuned for maximum efficiency. Smaller blocks means even less scanning, but uses more diskspace.

You can try different numbers to see what works best for you.

/SG

r5d4 SSCrazy Points: 2523 More actions · Answer 7

thanks for your reply Stefan, the code runs super fast, though I think I need to understand HOW before I implement it!

cheers

r

Stefan_G SSCertifiable Points: 6609 More actions · Answer 8

Please note that it is not enough just to run the posted code.

You must also write your own code to split up the ipCountryLookup in 64K-sized blocks.

I just wanted to make this super clear to avoid any misunderstandings.

good luck

/SG

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 9

Stefan_G (4/12/2010)
1) Add a nonclustered index on CountryCode, IpNumber on [my].[ipAddresses]

Ohhhh... be careful now. Unless that index is applied after all data is present and no new data will be inserted, that will likely cause massive fragmentation of the table because of the relatively low cardinality of the CountryCode.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stefan_G SSCertifiable Points: 6609 More actions · Answer 10

Jeff Moden (4/12/2010)
Stefan_G (4/12/2010)
1) Add a nonclustered index on CountryCode, IpNumber on [my].[ipAddresses]
Ohhhh... be careful now. Unless that index is applied after all data is present and no new data will be inserted, that will likely cause massive fragmentation of the table because of the relatively low cardinality of the CountryCode.

So what?

If fragmentation of this index would become a problem it is very easy to rebuild it.

Further more, the index does not really have a low cardinality.

What I expect to happen is that every time this update is run a very small percentage of the total amount of data in the table will have countrycode=null.

There might be 20 million rows in the table and perhaps 2000 new ip-numbers

So the selectivity for this index for finding rows where countrycode is null is very good.

I honestly dont see a problem here.

/SG

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 11

Stefan_G (4/12/2010)
Jeff Moden (4/12/2010)
Stefan_G (4/12/2010)
1) Add a nonclustered index on CountryCode, IpNumber on [my].[ipAddresses]
Ohhhh... be careful now. Unless that index is applied after all data is present and no new data will be inserted, that will likely cause massive fragmentation of the table because of the relatively low cardinality of the CountryCode.
So what?
If fragmentation of this index would become a problem it is very easy to rebuild it.
Further more, the index does not really have a low cardinality.
What I expect to happen is that every time this update is run a very small percentage of the total amount of data in the table will have countrycode=null.
There might be 20 million rows in the table and perhaps 2000 new ip-numbers
So the selectivity for this index for finding rows where countrycode is null is very good.
I honestly dont see a problem here.
/SG

I'm not even concerned with Nulls at this point. I agree that the CountryCode doesn't have anything even close to the low cardinality of something like a True/False column. Still, compared to the 22 million rows that the OP is talking about, it's low. It'll make for a fine nonclustered index. Using it as a clustered index can cause a lot of fragmentation of the data in the form of page splits especially for those "hot spot" countries that are adding IP's on a regular basis. I'd rather put the clustered index on a temporal column to prevent most of those page splits.

Heh... that's what. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stefan_G SSCertifiable Points: 6609 More actions · Answer 12

Jeff Moden (4/12/2010)
Stefan_G (4/12/2010)
1) Add a nonclustered index on CountryCode, IpNumber on [my].[ipAddresses]
It'll make for a fine nonclustered index. Using it as a clustered index can cause a lot of fragmentation of the data in the form of page splits especially for those "hot spot" countries that are adding IP's on a regular basis. I'd rather put the clustered index on a temporal column to prevent most of those page splits.

Well, I never suggested it should be a clustered index. 😛

On the other hand, I dont think it would be such a big problem even to make it a clustered index. For a narrow table like this page splits in the clustered index are not much worse than page splits in a nonclustered index.

/SG

r5d4 SSCrazy Points: 2523 More actions · Answer 13

big thanks chaps, given me a lot to think about.

cheers

r 🙂

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 14

Stefan_G (4/12/2010)
Jeff Moden (4/12/2010)
Stefan_G (4/12/2010)
1) Add a nonclustered index on CountryCode, IpNumber on [my].[ipAddresses]
It'll make for a fine nonclustered index. Using it as a clustered index can cause a lot of fragmentation of the data in the form of page splits especially for those "hot spot" countries that are adding IP's on a regular basis. I'd rather put the clustered index on a temporal column to prevent most of those page splits.
Well, I never suggested it should be a clustered index. 😛
On the other hand, I dont think it would be such a big problem even to make it a clustered index. For a narrow table like this page splits in the clustered index are not much worse than page splits in a nonclustered index.
/SG

Heh... ya know... you're right. My eyes were certainly playing tricks on me there. My apologies... :blush:

On the subject of a non-clustered index in a similar order at work... it caused very frequent timouts but, admittedly, that was on a table with a fair number of inserts. Still, putting the lower cardinality column at the end of the index still allowed seeks for the SELECTs without the extra page splits (extent splits in this case).

Anyway, thanks for the feedback, Stefan, and I apologize again for getting caught up on the clustered index thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)