MSSQL根据select语句的结果插入n次数据

Question

我有一个表格，其中一个列包含数字，表示每个房产拥有的车库数量。现在我需要为所有车库添加附加信息（大小、停车位数量、租金等），因此我必须通过插入与车库数量相同的记录到不同的表中来为每个车库创建记录。

我的目标是：

选择 ID，GarageCount 从 Properties 表 -- 运行下一条语句 GarageCount 次 插入 Garages (PropertyID) 的值为 Property.ID

我需要在 Properties 表中运行此语句以获取 GarageCount > 0 的所有属性。

Properties.ID 是主键，Garages.PropertyID 是外键。

- Daniel

---

如果你的查询返回1, 4，你想要插入1四次吗？ - HoneyBadger

这正是我正在寻找的。 - Daniel

Answer 1

你不需要以“循环”方式运行此操作。你可以使用公共表达式生成行，每个属性的行数与该属性中车库的数量相同：

with GarageRows as (
select id
    , garagecount
    , 0 [counter]
from Properties
union all
select p.id
    , 1
    , gr.counter + 1
from GarageRows gr
    inner join Properties p on gr.id = p.id
where gr.counter + 1 < p.garagecount)
insert into Garages(PropertyID)
select gr.ID
from GarageRows gr
where gr.garagecount > 0

如果您只是想测试上面CTE的结果，可以运行下面的查询，该查询为两个属性生成行，其中一个属性有2个车库，另一个属性有4个车库。

declare @properties table (id int, garagecount int)
insert @properties values (1, 2), (2, 4)

;with GarageRows as (
select id
    , garagecount
    , 0 [counter]
from @Properties
union all
select p.id
    , 1
    , gr.counter + 1
from GarageRows gr
    inner join @Properties p on gr.id = p.id
where gr.counter + 1 < p.garagecount)
select gr.ID
from GarageRows gr
where gr.garagecount > 0
order by gr.ID

- Radu Gheorghiu

---

使用 Numbers 表格重复一行要容易得多。 - Panagiotis Kanavos

@PanagiotisKanavos 是的，但如果他没有数字表，那么这个方法可以帮到他。 - Radu Gheorghiu

生成一个数字表是微不足道的，并且避免了复杂的CTE。 - Panagiotis Kanavos

@PanagiotisKanavos 我知道这很琐碎，网上有很多赞扬数字表方法的资源，但这是我解决问题的方法。数字表更容易和琐碎，但既然我确信有人会选择那个答案，我决定采用不同的方法（也可以练习我的递归CTE思维）。此外，我认为这不是一个复杂的CTE，我相信还有其他场景，存在真正复杂的CTE，在那里，一个数字表就像奇迹一样。 - Radu Gheorghiu

我喜欢这个解决方案的精细度。我也没有使用公共表达式（CTEs）遇到任何问题。 - Daniel

在答案发布之前，我正在考虑使用CROSS APPLY来完成这个任务。那样也是一个可行的解决方案吗？ - Daniel

Answer 2

您可以通过使用数字表来避免循环和复杂的查询。数字表是一个简单的表，其中包含从0开始的数字。

如果您与数字表连接，您的查询将变得非常简单：

INSERT INTO Garages (PropertyID) 
Select Property.ID
From Property inner join Numbers on Numbers.Number<Property.GarageCount
where garagecount>0 

这将重复相同的行，次数由GarageCount定义。

数字表可以在很多场景下使用，包括日期计算、字符串分割、识别范围中的差异和将循环转换为无限快速的集合操作。Aaron Bertrand撰写了许多文章解释如何生成和使用数字表。

Aaron Bertrand的文章展示了一种快速生成带有索引和压缩的数字表的方法（即使在SQL Server 2016 SP1的Express版本中也可以使用）。

DECLARE @UpperBound INT = 1000000;

;WITH cteN(Number) AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id]) - 1
  FROM sys.all_columns AS s1
  CROSS JOIN sys.all_columns AS s2
)
SELECT [Number] INTO dbo.Numbers
FROM cteN WHERE [Number] <= @UpperBound;

CREATE UNIQUE CLUSTERED INDEX CIX_Number ON dbo.Numbers([Number])
WITH 
(
  FILLFACTOR = 100,      
  DATA_COMPRESSION = ROW -- if the table is large enough to matter
);

- Panagiotis Kanavos