CODEWITHSUNDEEP
sql-servert-sql
Daniel
Daniel

Reputation: 1469

MSSQL insert n times, where n comes from a select

I have a table that contains a number in a column that defines how many garages a property have. Now I need to add additional information to all garages (size, number of parking spots, rental fee, etc.), so I have to create records for each garage in a different table by inserting as many records as there are garages.

What I'd like to accomplish:

SELECT ID, GarageCount FROM Properties

-- run next statement GarageCount times

INSERT INTO Garages (PropertyID) VALUES (Property.ID)

I need to run this to all properties in the Properties table where GarageCount > 0

Properties.ID is a PK, Garages.PropertyID is a FK.

Upvotes: 3

Views: 566

Answers (2)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

You don't need this to run "in a loop" style. You could just use a common table expression to generate rows, with as many rows per property as there are garages in that property:

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

If you just want to test the result of the CTE above, you can run the below query, which generates rows for two properties, one with 2 garages and one with 4 garages.

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

Upvotes: 3

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131581

You can avoid looping and complex queries by using a Numbers table. A Numbers is a simple table that contains numbers from 0 upwards.

Your query becomes trivial if you join with a Numbers table:

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

This will repeat the same row as many times as defined GarageCount.

A Numbers table can be used in a lot of scenarios, including date calculations, string splitting, identify gaps in ranges and converting loops to infinitely faster set operations. Aaron Bertrand has written many articles that explain how to generate and how to use Numbers table.

Aaron Bertrand's article shows a quick way to generate a Numbers table with an index and compression (which is available even in the Express editions with SQL Server 2016 SP1) :

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
);

Upvotes: 2

Related Questions