MSSQL - Create A Million Rows with Random Values

;WITH

TENS      (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

                  SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

                  SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),

MILLIONS  (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),

TALLY     (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)

--INSERT INTO #TestData

SELECT TOP (100000)

       AccountNumber = CASE WHEN (N%1000) = 0 THEN 1000 ELSE N%1000 END,

       Value         = CHAR(CONVERT(INT,RAND(CHECKSUM(NEWID()))*10)+64) +

                       CHAR(CONVERT(INT,RAND(CHECKSUM(NEWID()))*10)+64) +

                       CHAR(CONVERT(INT,RAND(CHECKSUM(NEWID()))*10)+64)

  FROM TALLY;

-- just the rows only

;WITH

TENS      (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

                  SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL

                  SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),

THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),

MILLIONS  (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),

TALLY     (M) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)

SELECT  M

  FROM TALLY;