Duplicated rows (x) amount of times in a table
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
Create a table containing just a number column and insert the numbers 1 ... max
, where max
is at least 10. Then cross join this table with the result set
INSERT INTO Table (SO, SO_Line, ...)
SELECT SO + '-' + CONVERT (varchar, Numbers.number), SO_Line, ...
FROM
Table
CROSS JOIN Numbers
WHERE Table.SO = 'ABC' AND Numbers.number <= 10
CROSS JOIN creates all combinations from the rows of the first and the second result set. So if you you have 3 rows of Table
and 10 of Numbers
you will get 3 * 10 = 30 combined resulting rows.
Note, that his allows you to easily specify a variable number of repetitions (up to max
) by just editing the number manually, through a parameter or from a column.