使用WHILE创建虚拟数据

小编典典

使用WHILE创建虚拟数据

sql

我尝试使用插入一些虚拟数据到我的表中WHILE，但是它的运行速度确实非常慢。

我在想，也许我编写的代码不正确，请您看看并确认一下吗？

-- Insert dummy data

DECLARE
    @i          int,
    @Content    int;
SET @i = 5001;

WHILE @i > 5000 AND @i < 10000
BEGIN
    SET @Content = ROUND(((10000-5000)*RAND()+5000),0)
    INSERT INTO dbo.CmsImagesContents
    (ContentId, Title, AltTag, Caption)
    VALUES
    (@Content,'Test Title', 'Test AltTag', 'Test Caption');
    SET @i = @i + 1;
END

阅读 143

2021-04-28

共1个答案

小编典典

如果只对所有4999行进行一次插入，则与在循环中执行4999个单独的插入语句相比，您将获得更好的性能。因此，如果您的表#T包含4999行，则只需调用以下命令：

INSERT INTO DBO.CmsImagesContents(ContentId, Title, AltTag, Caption)    
SELECT (ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 5000) + 5000 AS ContentID, 
        'Test Title' AS Title, 'Test AltTag' AS AltTag, 'Test Caption'  AS Caption
FROM #T1

如果您首先需要创建一个包含4999行的表，那么以下SQL将为您工作：

CREATE TABLE #T1
(
    N INT NOT NULL PRIMARY key
);

WITH L0 AS (SELECT 1 AS N UNION ALL SELECT 1), 
    L1 AS (SELECT A.N FROM L0 AS A CROSS JOIN L0 AS B),
    L2 AS (SELECT A.N FROM L1 AS A CROSS JOIN L1 AS B),
    L3 AS (SELECT A.N FROM L2 AS A CROSS JOIN L2 AS B),
    L4 AS (SELECT A.N FROM L3 AS A CROSS JOIN L3 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM L4)

INSERT INTO #T1( N )
SELECT N
FROM Nums
WHERE n < 10000 AND n>5000;

2021-04-28