如何执行SQL脚本随机在用户表中生成N条记录?
表结构:用户名,密码,二级密码,昵称,邮箱,性别,电话号码,积分。其中用户名,密码,二级密码和昵称是必填的。昵称最好能是汉字。类型是VARCHAR30的。
SELECT 用户名 = 'U' + RIGHT('000000' + CAST(n AS varchar(10)),6),
密码 = HASHBYTES('md5',CAST(n AS varchar(10))),
二级密码 = HASHBYTES('md5',CAST(ABS(CHECKSUM(NEWID())) AS varchar(10))),
昵称 = NCHAR(19968 + ABS(CHECKSUM(NEWID())) % 20902)+NCHAR(19968 + ABS(CHECKSUM(NEWID())) % 20902)+NCHAR(19968 + ABS(CHECKSUM(NEWID())) % 20902),
邮箱 = CHAR(97 + ABS(CHECKSUM(NEWID())) % 26) + CHAR(97 + ABS(CHECKSUM(NEWID())) % 26) + CHAR(97 + ABS(CHECKSUM(NEWID())) % 26) + CHAR(97 + ABS(CHECKSUM(NEWID())) % 26) + '@' + CHAR(97 + ABS(CHECKSUM(NEWID())) % 26) + CHAR(97 + ABS(CHECKSUM(NEWID())) % 26) + CHAR(97 + ABS(CHECKSUM(NEWID())) % 26) + '.com' + CASE WHEN ABS(CHECKSUM(NEWID())) % 10 = 0 THEN '.cn' ELSE '' END,
性别 = CASE WHEN ABS(CHECKSUM(NEWID())) % 2 = 0 THEN 'M' ELSE 'F' END,
电话号码 = '13' + RIGHT('000000000' + CAST(ABS(CHECKSUM(NEWID())) AS varchar(10)), 9),
积分 = 1000 + ABS(CHECKSUM(NEWID())) % 9000
FROM Nums WHERE n BETWEEN 1 AND 100