Geo ip import to SQL Server

ImportGeoIpsToSql

-- Download the country ip csv file from https://db-ip.com/db/ and save
-- the csv file in C:\temp\dbip-country.csv or change the script below

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GeoIps]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[GeoIps](
	[fromIp] [binary](4) NOT NULL,
	[toIp] [binary](4) NOT NULL,
	[fromIpDisplay] [varchar](15) NOT NULL,
	[toIpDisplay] [varchar](15) NOT NULL,
	[CountryCode] [char](2) NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [IX_GeoIps]    Script Date: 11/12/2014 5:10:46 μμ ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[GeoIps]') AND name = N'IX_GeoIps')
CREATE UNIQUE CLUSTERED INDEX [IX_GeoIps] ON [dbo].[GeoIps]
(
	[fromIp] ASC,
	[toIp] ASC
)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

truncate table GeoIps

Create Table #tmpGeoData (
	[fromIp] [nvarchar](50) NOT NULL,
	[toIp] [nvarchar](50) NOT NULL,
	[countryCode] [nvarchar](50) NOT NULL
)


BULK
 INSERT #tmpGeoData
FROM 'C:\temp\dbip-country.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
  update #tmpGeoData set
  toIp = SUBSTRING ( toIp, 2, len(toIp)-2),
  fromIp = SUBSTRING ( fromIp, 2, len(fromIp)-2),
  countryCode = SUBSTRING ( countryCode, 2, len(countryCode)-2)

insert into GeoIps ([fromIp]
           ,[toIp]
           ,[fromIpDisplay]
           ,[toIpDisplay]
           ,[CountryCode])
select 
CAST( CAST( PARSENAME( fromIp, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( fromIp, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( fromIp, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( fromIp, 1 ) AS INTEGER) AS BINARY(1)),
				CAST( CAST( PARSENAME( toIp, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( toIp, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( toIp, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( toIp, 1 ) AS INTEGER) AS BINARY(1)),
				fromIp, toIp, countryCode
from #tmpGeoData
-- Avoid adding the ipv6 addresses and the ones without a country code
where len(countryCode)>0 and fromIp not like '%:%'


DROP TABLE #tmpGeoData