SQL Server快速向表中插入200万条测试数据
USE master go IF EXISTS (SELECT * FROM sysdatabases WHERE name='DBAdmin' ) DROP DATABASE DBAdmin GO CREATE DATABASE DBAdmin go USE DBAdmin; GO /* Setup script to create the sample table and fill it with sample data. */ CREATE TABLE [dbo].[Customers] ( [CustomerID] [INT] IDENTITY(1, 1) NOT NULL, [CustomerNumber] [CHAR](8) NULL, [CustomerName] [VARCHAR](50) NULL, [CustomerCity] [VARCHAR](20) NULL, CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ([CustomerID] ASC) ) ON [PRIMARY]; GO TRUNCATE TABLE Customers; GO DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE; GO ; /*****运用CTE递归插入,速度较快,邀月注***********************/ WITH Seq (num, CustomerNumber, CustomerName, CustomerCity) AS (SELECT 1, CAST('00000000' AS CHAR(8)), CAST('Customer 0' AS NVARCHAR(50)), CAST('X-City' AS NVARCHAR(20)) UNION ALL SELECT num + 1, CAST(REPLACE(STR(num, 8), ' ', '0') AS CHAR(8)), CAST('Customer ' + STR(num, 8) AS NVARCHAR(50)), CAST(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20)) FROM Seq WHERE num <= 2000000) INSERT INTO Customers ( CustomerNumber, CustomerName, CustomerCity ) SELECT CustomerNumber, CustomerName, CustomerCity FROM Seq OPTION (MAXRECURSION 0);