如何執行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