Duplicated rows (x) amount of times in a table 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 &lt; @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?
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