SQL Server – How to Duplicate Rows X Times in a Table

sql serversql-server-2016

I have a table that looks like the following:

╔══════════╦═══════════╦════════╦
║      SO  ║   SO_Line ║  ...   ║ 
╠══════════╬═══════════╣════════║
║      ABC ║        1  ║        ║
║      ABC ║        3  ║        ║
║      ABC ║        5  ║        ║
║      DEF ║        1  ║        ║
║      DEF ║        2  ║        ║
╚══════════╩═══════════╝════════╝

What I'd like to do is duplicate a result set (x) amount of times. For instance, given this result set: SELECT * FROM Table WHERE SO = 'ABC', I'd like to duplicate that result set 10 times. Basically I want to keep all of the data in all of the other columns the same, but I'll change the primary key to be something else (for instance, ABC-1). My final table after duplicating that set would be like this:

╔══════════╦═══════════╦════════╦
║      SO  ║   SO_Line ║  ...   ║ 
╠══════════╬═══════════╣════════║
║      ABC ║        1  ║  ...   ║
║      ABC ║        3  ║  ...   ║
║      ABC ║        5  ║  ...   ║
║    ABC-1 ║        1  ║  ...   ║
║    ABC-1 ║        3  ║  ...   ║
║    ABC-1 ║        5  ║  ...   ║
╚══════════╩═══════════╝════════╝

Normally the way I would do this is by inserting the records from the result set into a temporary table and then assigning a row number to each row. Then I'd iterate through the temporary table using something like WHILE @i < @RowCount and do an insert into the main table using @i to increment the PK. I'm basically asking if A) that's the most reasonable way to accomplish this, and if not, B) is there a more efficient way of handling this?

Best Answer

You can get it using a table value or a value list, and a CROSS JOIN.

INSERT INTO foo
SELECT CONCAT(SO, t.x) as SO, SO_Line
FROM   foo
CROSS JOIN (VALUES('-1'),('-2'),('-3'),('-4'),('-5')) t(x)
WHERE  SO = 'ABC';
GO

15 rows affected

SELECT * FROM   foo;
GO

SO    | SO_Line
:---- | ------:
ABC   |       1
ABC   |       3
ABC   |       5
DEF   |       1
DEF   |       2
ABC-1 |       1
ABC-2 |       1
ABC-3 |       1
ABC-4 |       1
ABC-5 |       1
ABC-1 |       3
ABC-2 |       3
ABC-3 |       3
ABC-4 |       3
ABC-5 |       3
ABC-1 |       5
ABC-2 |       5
ABC-3 |       5
ABC-4 |       5
ABC-5 |       5

dbfiddle here

Instead of use a value list, you could use an INLINE User defined function like this:

CREATE FUNCTION tvValues(@Num int)
RETURNS table
AS
RETURN 
(
     SELECT TOP (@Num) ROW_NUMBER() OVER (ORDER BY S.[object_id]) [item]
     FROM    sys.all_objects S
)
GO

Then convert your query to:

INSERT INTO foo
SELECT CONCAT(SO, '-', t.item) as SO, SO_Line
FROM   foo
CROSS JOIN tvValues(7) t
WHERE  SO = 'ABC';
GO

NOTE: I've used tvValues(7) but you can use other values.

This is the result:

SELECT * FROM   foo;
GO

SO    | SO_Line
:---- | ------:
ABC   |       1
ABC   |       3
ABC   |       5
DEF   |       1
DEF   |       2
ABC-1 |       1
ABC-2 |       1
ABC-3 |       1
ABC-4 |       1
ABC-5 |       1
ABC-6 |       1
ABC-7 |       1
ABC-1 |       3
ABC-2 |       3
ABC-3 |       3
ABC-4 |       3
ABC-5 |       3
ABC-6 |       3
ABC-7 |       3
ABC-1 |       5
ABC-2 |       5
ABC-3 |       5
ABC-4 |       5
ABC-5 |       5
ABC-6 |       5
ABC-7 |       5

dbfiddle here

Related Question