在SQL Server中如何生成随机nvarchars

问题描述：

我已经通过一些链接的帮助，在这个网站上创建一个存储过程如下：
How to insert Huge dummy data to Sql server
What is the most efficient way to generate 8 character random alphanumeric string in TSQL?在SQL Server中如何生成随机nvarchars

我的存储过程：

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

执行时，会插入@noOfRecords（由用户给出）与记录相同的名称生成为@r。我怎样才能使@r不同随机nvarchar为每个生成的记录。

你们是不是要找到一种方法来生成随机字符串或要调试所提供的代码。还有很多其他方法可以实现这一目标。 – FLICKER

一直提到您正在使用的数据库或软件的版本 – FLICKER

我试图在表格中插入例如100000个不同的名称。我想使用sql性能，并需要我的表中的大数据。 –

答

尝试为您neednesses下面的代码片段适应：

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;

在SQL Server中如何生成随机nvarchars

相关推荐