快速生成 IP 表

IF OBJECT_ID('[dbo].[ip_area]') IS NOT NULL
	DROP TABLE [dbo].[ip_area]
GO
CREATE TABLE [dbo].[ip_area](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[ip] [varchar](20) NULL,
	[area] [nvarchar](300) NULL,
	[corp] [nvarchar](300) NULL,
	[isPrivate] [bit] NOT NULL DEFAULT ((0)),
	PRIMARY KEY CLUSTERED 
	(
		[id] ASC
	)
)
GO
--全部的IP是 2^32 ( 4 294 967 296 ), 太大不宜一次性 cross apply 插入, 否则事务太大， 出错回滚不易
SET NOCOUNT ON
DECLARE @1 INT,@2 INT,@beginTime DATETIME,@endTime DATETIME
SET @1=0
SET @2=0
SELECT number INTO #3 FROM [master]..spt_values WHERE TYPE='P' AND number BETWEEN 0 AND 255
SELECT number INTO #4 FROM [master]..spt_values WHERE TYPE='P' AND number BETWEEN 0 AND 255

WHILE @1<=255
BEGIN
	SET @beginTime=GETDATE()
	SET @2=0
	WHILE @2<=255
	BEGIN
		INSERT INTO ip_area(ip)
		SELECT CONCAT( @1, '.' , @2 , '.' , #3.number , '.' , #4.number ) 
		FROM #3 CROSS APPLY #4
		ORDER BY #3.number, #4.number
		SET @2=@2+1	
	END
	
	SET @endTime=GETDATE()
	SELECT @1 AS ip1,@beginTime AS beginTime, @endTime AS endTime, DATEDIFF(second, @beginTime, @endTime) AS elapsedSeconds
	SET @1=@1+1
END

如果机器内存大， 磁盘性能好， 可以用（更快一点）：

--全部的IP是 2^32 ( 4 294 967 296 ), 太大不宜一次性 cross apply 插入, 否则事务太大， 出错回滚不易
SET NOCOUNT ON
DECLARE @1 INT,@beginTime DATETIME,@endTime DATETIME
SET @1=0
;WITH t2 AS (
	SELECT number FROM [master]..spt_values WHERE TYPE='P' AND number BETWEEN 0 AND 255
), t3 AS (
	SELECT number FROM [master]..spt_values WHERE TYPE='P' AND number BETWEEN 0 AND 255
), t4 AS(
	SELECT number FROM [master]..spt_values WHERE TYPE='P' AND number BETWEEN 0 AND 255
)
SELECT CONCAT(t2.number,'.',t3.number,'.',t4.number) AS ip234 INTO #234 FROM t2 CROSS APPLY t3 CROSS APPLY t4 ORDER BY t2.number,t3.number,t4.number

WHILE @1<=255
BEGIN
	SET @beginTime=GETDATE()
	INSERT INTO ip_area(ip)
	SELECT CONCAT( @1, '.', #234.ip234 ) 
	FROM #234
	
	SET @endTime=GETDATE()
	SELECT @1 AS ip1,@beginTime AS beginTime, @endTime AS endTime, DATEDIFF(second, @beginTime, @endTime) AS elapsedSeconds
	SET @1=@1+1
END