gpt4 book ai didi

sql - 使用 WHILE 创建虚拟数据

转载 作者:行者123 更新时间:2023-12-04 23:44:21 25 4
gpt4 key购买 nike

我尝试使用 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

最佳答案

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

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;

关于sql - 使用 WHILE 创建虚拟数据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/3572537/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com