SQL statement for creating an IP address data table

Objective: To collect and sort out the country, city, and region information corresponding to all individual IP addresses

Test Description: The database is too large: 4129151.0009765625 K = 4032.37402439117431640625 m

Results: It seems that the query speed is not very convenient and faster, and there is no need to sort out such detailed data. It is better to use the IP address database of another user.

Proof of failure: where does the commercial IP database come from? Is the qq ip database suitable for small and medium-sized enterprises? I don't know how big the damage to my hard disk is. I wrote MB to d in five minutes.

Test execution statement: -- exec sp_createiptable
Select top 1 * From ipdata order by ID DESC
Id = 1508872; IP = "0.23.6.7" query time: "5 minutes"

Technical Study: familiar with SQL statement usage during SQL Storage

Stored Procedure Code:

Create procedure sp_createiptable
As

-- Create an IP data table
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [iptable] ') and objectproperty (ID, n'isusertable') = 1)
Drop table [DBO]. [iptable]

Create Table [DBO]. [iptable] (
[ID] int identity (1, 1) primary key clustered not null,
[IP] [nvarchar] (15) Collate chinese_prc_ci_as null,
[Ipcountry] [nvarchar] (20) Collate chinese_prc_ci_as null,
[Ipprovince] [nvarchar] (20) Collate chinese_prc_ci_as null,
[IPaddress] [nvarchar] (20) Collate chinese_prc_ci_as null
) On [primary]
-- Query data in all time periods to the temporary data table
Declare @ tempip nvarchar (16)

Declare @ firstnumber int
Declare @ secondnumber int
Declare @ thirdnumber int
Declare @ fourthnumber int

-- Initialize favorite Parameters

Set @ firstnumber = 0
Set @ secondnumber = 0
Set @ thirdnumber = 0
Set @ fourthnumber = 0

While @ firstnumber: <256
Begin
While @ secondnumber <256
Begin
While @ thirdnumber <256
Begin
While @ fourthnumber <256
Begin
Set @ tempip = convert (nvarchar (3), @ firstnumber) + '. '+ convert (nvarchar (3), @ secondnumber) + '. '+ convert (nvarchar (3), @ thirdnumber) + '. '+ convert (nvarchar (3), @ fourthnumber)
Insert into iptable (IP) values (@ tempip)
Set @ fourthnumber = @ fourthnumber + 1
End
If @ fourthnumber = 256 set @ fourthnumber = 0
Set @ thirdnumber = @ thirdnumber + 1
End
If @ thirdnumber = 256 set @ thirdnumber = 0
Set @ secondnumber = @ secondnumber + 1
End
If @ secondnumber = 256 set @ secondnumber = 0
Set @ firstnumber = @ firstnumber + 1
End

-- Select * From iptable
Go