How to generate random nvarchars in sql server

Question

I have created a stored procedure by the help of some links on this site as below:
How to insert Huge dummy data to Sql server
What is the most efficient way to generate 8 character random alphanumeric string in TSQL?

My stored procedure:

IF  EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DUMMY_INSERT]') AND type in (N'P', N'PC'))
BEGIN
    DROP PROCEDURE  DUMMY_INSERT
END
GO
CREATE PROCEDURE DUMMY_INSERT (
@noOfRecords INT
)
AS
BEGIN

DECLARE @r varchar(8)

SELECT @r = coalesce(@r, '') +CHAR(
CASE WHEN r between 0 and 9 THEN 48
WHEN r between 10 and 35 THEN 55
ELSE 61 END + r)
FROM
master..spt_values
CROSS JOIN
(SELECT CAST(RAND(ABS(CHECKSUM(NEWID()))) *61 as int) r) a
WHERE type = 'P' AND number < 8

DECLARE @count int
SET @count = 1;

WHILE (@count < @noOfRecords)
BEGIN
    INSERT INTO Tbl_Customer(name)
     VALUES(@r);

     SET @count = @count + 1;
END
END

When execution, it will insert @noOfRecords (given by user) records with Same names generated as @r. How can I make @r different random nvarchar for each generated record.

Are you trying to find a way to generate random string or you want to debug the provided code. There are many other ways to achieve that goal. — FLICKER, Oct 17 '17 at 16:19
I am trying to insert for example 100000 different names in a table. I want to work with sql performances and need big data in my tables. — , Oct 17 '17 at 16:25
Is it important that you use the full range of `a-zA-Z`? To generate an 8 character random alpha numeric string you can just use `CONVERT(CHAR(8),CRYPT_GEN_RANDOM(4),2)` which is much simpler though has fewer permutations (as only uses hex characters) — Martin Smith, Oct 17 '17 at 17:09
@Blorgbeard, the result is the same as original SP. It produce **same** names. — , Oct 17 '17 at 17:25
But, Is there any solution to insert just characters and NOT digits? — , Oct 17 '17 at 17:38

score 0 · Answer 1 · answered Oct 17 '17 at 20:47

Try to adapt for your neednesses the following snippet:

IF OBJECT_ID(N'dbo.DUMMY_INSERT', N'P') IS NOT NULL DROP PROCEDURE dbo.DUMMY_INSERT;
GO
CREATE PROCEDURE dbo.DUMMY_INSERT(@noOfRecords INT, @chars INT = 8) AS
WITH
  cc AS(SELECT 1 c UNION ALL SELECT c + 1 FROM cc WHERE c < @chars),
  rr AS(SELECT 1 r UNION ALL SELECT r + 1 FROM rr WHERE r < @noOfRecords),
  rc AS(SELECT r, ABS(CHECKSUM(NEWID())) % 61 rnd FROM rr CROSS JOIN cc)
  SELECT
    (SELECT NCHAR(IIF(rnd < 10, 48, IIF(rnd < 36, 55, 61))) [text()]
     FROM rc WHERE rc.r = rr.r
     FOR XML PATH('')) rndrow
  FROM rr;
GO
IF OBJECT_ID(N'dbo.Tbl_Customer', N'U') IS NOT NULL DROP TABLE dbo.Tbl_Customer;
GO
CREATE TABLE dbo.Tbl_Customer(name NVARCHAR(30));
GO
INSERT dbo.Tbl_Customer(name) EXEC dbo.DUMMY_INSERT 20;
GO
SELECT name FROM dbo.Tbl_Customer;

How to generate random nvarchars in sql server

1 Answers1