【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.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62

-- 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.