First set up your data

CREATE TABLE #Required (

KeyHD INT --PRIMARY KEY CLUSTERED

, CountInserted INT

);

INSERT INTO #Required (KeyHD, CountInserted)

VALUES (1, 2), (2, 5), (3, 7);

Now, using a CTE

WITH

L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),

L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B)

SELECT r.KeyHD, 'My Value'

FROM #Required AS r

CROSS APPLY (

SELECT TOP(r.CountInserted) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n

FROM L5

) AS n

You can also convert the CTE into a virtual Tally Table, which is then re-usable

CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE

AS

RETURN

WITH

L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),

L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),

Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum

FROM L5)

SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n

FROM Nums

ORDER BY rownum;

GO

Now, using the virtual tally table

SELECT r.KeyHD, 'Some Value'

FROM #Required AS r

CROSS APPLY dbo.GetNums(1, CountInserted) AS n;