在sql中向表中插入10条记录的过程

Question

我尝试过这个存储过程，用随机数据将10条记录插入到分类账表中，但它不起作用，成功执行后，它不会在表中插入任何内容。我做错了什么？？？有人能帮我吗…

CREATE PROCEDURE FillLedger  
AS 
BEGIN 
SET NOCOUNT ON 
DECLARE @c INT = 1,
@itemNo int = floor(rand()*11),
@quantity  int = floor(rand()*50) + 50,
@date  datetime = '2015-01-01'
While @c > 11 
BEGIN 
INSERT INTO dbo.LEDGER
(                    
item_id           ,
QTY               ,
PostingDate                 
) 
VALUES 
( 
@itemNo,
@quantity,
@date
) 
Set @c = @c + 1 ;  
END
END 
GO

Answer 1

假设您希望WHERE子句为：

While @c < 11 

您的代码可能还有其他错误。

您确实意识到，这会将相同的10行插入到表中。您可以初始化WHERE正文之外的值，因此列值永远不会更改。

Answer 2

您可以使用递归查询而不是循环来完成此操作。假设SQL Server：

with cte as (
select @itemno as item_id, @quantity as qty, @date as postingdate, 1 as c
union all
select item_id, qty, postingdate, c + 1 from cte where c < 10
)
insert into dbo.ledger(item_id, qty, postingdate)
select tem_id, qty, postingdate from cte

---

编辑：如果你想要10条带有随机项目id和数量的记录，那么你需要在子查询中移动随机分配：

with cte as (
select floor(rand()*11) as item_id, floor(rand()*50) + 50 as qty, @date as postingdate, 1 as c
union all
select floor(rand()*11), floor(rand()*50) + 50, postingdate, c + 1 from cte where c < 10
)
insert into dbo.ledger(item_id, qty, postingdate)
select tem_id, qty, postingdate from cte