【SqlServer】导入MaxMind中的IP数据(.csv)文件到SQL Server数据库（转）

原文链接：http://creativeclr.com/blog/importing-maxmind-ip-database-into-sql-server

 

 

I've recently found a very accurate free ip database provided by MaxMind.

I have tried some methods to import this csv data into my Sql Server database, but what I have found that either the example was broken or wasn't complete (files were missing from the script download).

So I started to digg into the issue and found a simple and fast way of importing the 2 csv's into my sql server database.

1. First, you need to edit the 2 csv files and remove all quotes ("). Since the files are fairly large, use a smart editor (I used Notepad2).
2. Run the following code. Don't forget to replace the paths to the files with your own.

-- Geo IP V4
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'GeoIP')BEGIN
DROP TABLE GeoIP
END

CREATE TABLE GeoIP (
	startIpNum bigint,
	endIpNum bigint,
	locId  bigint
)

-- first remove ALL quotes from csv to import
BULK INSERT GeoIP FROM 'your\path\to\GeoLiteCity-Blocks.csv' WITH (FIRSTROW = 2, FIELDTERMINATOR=',', ROWTERMINATOR = '0x0a')

CREATE CLUSTERED INDEX Geo_IP_Look
ON GeoIP
([StartIpNum], [endIpNum],  [locId])

-- Geo IP V6

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'GeoIPSix')BEGIN
DROP TABLE GeoIPSix
END

CREATE TABLE GeoIPSix(
	startIpNum bigint,
	endIpNum bigint,
	locId  bigint
)

-- first remove ALL quotes from csv to import
BULK INSERT GeoIP FROM 'your\path\to\GeoLiteCity-Blocks-IPv6.csv' WITH (FIRSTROW = 2, FIELDTERMINATOR=',', ROWTERMINATOR = '0x0a')

CREATE CLUSTERED INDEX Geo_IP_Look
ON GeoIP
([StartIpNum], [endIpNum],  [locId])


-- Geo Loc

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'GeoLoc')BEGIN
DROP TABLE GeoLoc
END


CREATE TABLE GeoLoc (
	 locId bigint,
	 country nvarchar(2),
	 region nvarchar(3),
	 city nvarchar(255),
	 postalCode nvarchar(10),
	 latitude nvarchar(15),
	 longitude nvarchar(15),
	 metroCode nvarchar(15),
	 areaCode nvarchar(15)
)

BULK INSERT GeoLoc FROM 'your\path\to\GeoLiteCity-Location.csv' WITH (FIRSTROW = 2, FIELDTERMINATOR=',', ROWTERMINATOR = '0x0a')  

CREATE CLUSTERED INDEX Geo_Info_Look
ON GeoLoc
([locId], [country], [region], [city], [latitude], [longitude])

　　

I tested the code on my SQL Server 2008 R2 db, and it all worked OK.

Update: you might want to remove the headers from the maxmind csv's.