如何快速为SQL Server Address表生成千万级测试/样本记录？

前提准备 #

先确保Address表已创建（若已存在可跳过）：

CREATE TABLE Address (
    AddressID VARCHAR(20),
    FirstLine VARCHAR(400),
    SecondLine VARCHAR(400),
    State VARCHAR(100),
    Country VARCHAR(50)
);

方法一：递归CTE+随机函数生成 #

利用递归CTE生成连续数字序列，结合NEWID()、CHECKSUM()生成随机字段内容，逻辑清晰易调整：

WITH Numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Numbers WHERE n < 10000000
)
INSERT INTO Address (AddressID, FirstLine, SecondLine, State, Country)
SELECT 
    -- 生成带固定前缀的唯一AddressID
    'AddrID' + RIGHT('0000000000' + CAST(n AS VARCHAR), 10),
    -- 生成50-350长度的随机字符串作为第一行地址
    SUBSTRING(NEWID(), 1, ABS(CHECKSUM(NEWID())) % 300 + 50),
    -- 三分之一概率为空，否则生成20-220长度的随机字符串作为第二行地址
    CASE WHEN ABS(CHECKSUM(NEWID())) % 3 = 0 THEN '' ELSE SUBSTRING(NEWID(), 1, ABS(CHECKSUM(NEWID())) % 200 + 20) END,
    -- 随机选择美国州缩写
    CHOOSE(ABS(CHECKSUM(NEWID())) % 50 + 1, 'AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'),
    'US'
FROM Numbers
OPTION (MAXRECURSION 0); -- 必须设置，突破递归默认深度限制

方法二：系统表笛卡尔积快速生成 #

利用系统表sys.all_columns的笛卡尔积生成海量行，比递归CTE速度更快，适合超大规模数据生成：

INSERT INTO Address (AddressID, FirstLine, SecondLine, State, Country)
SELECT TOP 10000000
    'AddrID' + RIGHT('0000000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR), 10),
    SUBSTRING(NEWID(), 1, ABS(CHECKSUM(NEWID())) % 300 + 50),
    CASE WHEN ABS(CHECKSUM(NEWID())) % 3 = 0 THEN '' ELSE SUBSTRING(NEWID(), 1, ABS(CHECKSUM(NEWID())) % 200 + 20) END,
    CHOOSE(ABS(CHECKSUM(NEWID())) % 50 + 1, 'AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'),
    'US'
FROM sys.all_columns c1
CROSS JOIN sys.all_columns c2
CROSS JOIN sys.all_columns c3
OPTION (MAXDOP 8); -- 利用多CPU核心加速插入

优化：生成真实风格的地址 #

如果需要更贴近真实的地址内容，可以预先创建字典表，组合生成真实格式的地址：

1. 创建地址字典表 #

-- 街道前缀表
CREATE TABLE StreetPrefixes (Prefix VARCHAR(50))
INSERT INTO StreetPrefixes VALUES 
('Main'), ('Oak'), ('Maple'), ('Cedar'), ('Elm'), 
('Pine'), ('Washington'), ('Lake'), ('Hill'), ('River');

-- 街道类型表
CREATE TABLE StreetTypes (Type VARCHAR(20))
INSERT INTO StreetTypes VALUES 
('St'), ('Ave'), ('Rd'), ('Ln'), ('Dr'), 
('Blvd'), ('Ct'), ('Pl'), ('Way'), ('Ter');

2. 生成真实风格数据 #

INSERT INTO Address (AddressID, FirstLine, SecondLine, State, Country)
SELECT TOP 10000000
    'AddrID' + RIGHT('0000000000' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR), 10),
    -- 组合生成"门牌号 前缀 类型"的真实街道格式
    CONCAT(ABS(CHECKSUM(NEWID())) % 9999 + 1, ' ',
           (SELECT TOP 1 Prefix FROM StreetPrefixes ORDER BY NEWID()), ' ',
           (SELECT TOP 1 Type FROM StreetTypes ORDER BY NEWID())),
    -- 生成真实格式的公寓号，三分之一概率为空
    CASE WHEN ABS(CHECKSUM(NEWID())) % 3 = 0 THEN '' ELSE CONCAT('Apt ', ABS(CHECKSUM(NEWID())) % 999 + 1) END,
    CHOOSE(ABS(CHECKSUM(NEWID())) % 50 + 1, 'AL','AK','AZ','AR','CA','CO','CT','DE','FL','GA','HI','ID','IL','IN','IA','KS','KY','LA','ME','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VT','VA','WA','WV','WI','WY'),
    'US'
FROM sys.all_columns c1
CROSS JOIN sys.all_columns c2
CROSS JOIN sys.all_columns c3
OPTION (MAXDOP 8);

关键注意事项 #

• 磁盘空间：1000万条记录约占用10GB磁盘空间，提前确认磁盘容量充足。
• 性能优化：插入前关闭表上的索引、触发器，完成后再重建索引，可大幅提升插入速度。
• 超时设置：在SSMS中设置查询执行超时为0（无限制），避免中途因超时中断。

内容的提问来源于stack exchange，提问作者user11251420