Multiplying/duplicating/triplicating rows in SQL efficiently

July 5, 2023

I would like to know how to efficiently (computation-wise) duplicate/triplicate/quadruple rows in a given SQL table. I know I could build a small table containing just a series of INT (say, from 1 to 4 to quadruple the existing rows) and use CROSS JOIN to do the job as below (example can also be found here) —

CREATE TABLE ForgeRock
    ([productName] varchar(13), [description] varchar(57))
;

INSERT INTO ForgeRock
    ([productName], [description])
VALUES
    ('OpenIDM', 'Platform for building enterprise provisioning solutions'),
    ('OpenAM', 'Full-featured access management'),
    ('OpenDJ', 'Robust LDAP server for Java')
;

CREATE TABLE Multiplier (
    [Times] int
);

INSERT INTO Multiplier (Times)
VALUES 
(1),
(2),
(3),
(4)
;

SELECT
  productName,
  description
FROM
  ForgeRock
CROSS JOIN 
  Multiplier

but this is a bit round-about way in my opinion (because we have to build another table with integers in it) and I’m wondering if anyone has a more elegant and efficient approach (preferably using just simple SQL such as ISO 9075 compliant one).

Thank you in advance for your suggestions!

>Solution :

Your approach is the standard way to go. If you want to avoid creating a table of numbers, you can declare it within the query using a row-constructore syntax.

In SQL Server, you would use VALUES():

SELECT f.productName, f.description
FROM ForgeRock f
CROSS JOIN ( VALUES (1), (2), (3), (4) ) v(times)

An alternative is to use a recursive query to duplicate the rows – but this is likely to be less efficient, especially if a large number of rows need to be duplicated:

with cte as (
    SELECT productName, description, 0 lvl, 4 max_lvl FROM ForgeRock
    UNION ALL 
    SELECT productName, description, lvl + 1, max_lvl FROM cte WHERE lvl < max_lvl
)
SELECT productName, description FROM cte