Intro
This guide aims to demonstrate how to load data from the IP2Location DB24 (IPv6) CSV file into a Microsoft SQL Server 2017 table.
If you’re using an earlier version of the SQL Server, you will need to refer to our FAQ page for technical information about creating the table and importing the data.
Downloading the DB24 database for IPv6
You can subscribe for the DB24 database at https://www.ip2location.com/database/ip2location if you don’t have the subscription yet. If you’re already subscribed, just login to https://www.ip2location.com/log-in and download the DB24 CSV file (IPv6).
Once you have downloaded the zipped file containing the CSV, extract the CSV and store it somewhere on your computer.
Creating the database and table
Run the following SQL commands to create the database called ip2location and a table called ip2location_db24_ipv6.
CREATE DATABASE ip2location GO USE ip2location GO CREATE TABLE [ip2location].[dbo].[ip2location_db24_ipv6]( [ip_from] char(39) NOT NULL, [ip_to] char(39) NOT NULL, [country_code] char(2) NOT NULL, [country_name] nvarchar(64) NOT NULL, [region_name] nvarchar(128) NOT NULL, [city_name] nvarchar(128) NOT NULL, [latitude] float NOT NULL, [longitude] float NOT NULL, [zip_code] nvarchar(30) NOT NULL, [time_zone] nvarchar(8) NOT NULL, [isp] nvarchar(256) NOT NULL, [domain] nvarchar(128) NOT NULL, [net_speed] nvarchar(8) NOT NULL, [idd_code] nvarchar(5) NOT NULL, [area_code] nvarchar(30) NOT NULL, [weather_station_code] nvarchar(10) NOT NULL, [weather_station_name] nvarchar(128) NOT NULL, [mcc] nvarchar(256) NOT NULL, [mnc] nvarchar(256) NOT NULL, [mobile_brand] nvarchar(128) NOT NULL, [elevation] int(10) NOT NULL, [usage_type] nvarchar(11) NOT NULL ) ON [PRIMARY] GO CREATE CLUSTERED INDEX [ip_to] ON [ip2location].[dbo].[ip2location_db24_ipv6]([ip_to]) ON [PRIMARY] GO
Importing the data
Run the following SQL commands to import data from the CSV file into the table. Modify the below to reflect the path where you saved the extracted CSV file earlier.
BULK INSERT ip2location_db24_ipv6
FROM 'C:\your_folder_name\IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE.CSV'
WITH
(
FORMAT = 'CSV',
FIELDQUOTE = '"',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0D0A',
TABLOCK
)
GO
update ip2location_db24_ipv6 set
ip_from = (SUBSTRING(REPLICATE('0', 39), 1, 39 - LEN(ip_from)) + ip_from),
ip_to = (SUBSTRING(REPLICATE('0', 39), 1, 39 - LEN(ip_to)) + ip_to)
GO
That’s all. Now you can query the table with an IP number that’s left padded with zeros to a total length of 39 characters. This is due to the fact that SQL Server does not support an integer with 39 digits. So, we are padding the IP number with zeros to be able to sort the field properly.