How to duplicate rows and make multiple inserts for each row for a specific column using SQL?

Question

Is there a way in SQL to duplicate each row and and enter a constant value before each result as shown:

This is what I have:

    Name        Height            
1   John        6.5         
2   Ben         7.0         
3   Mike        6.6
4   Adam        7.1

I want to add new row infront of each row in the data and specify values (ex. 1, 5, 5, 5….) as seen below:

    Name        Height            
1   JohnStart   1
2   John        6.5
3   BenStart    5                       
4   Ben         7.0
5   MikeStart   5                       
6   Mike        6.6
7   AdamStart   5
8   Adam        7.1

I have tried the insert function but it’s not working

INSERT INTO mydata (Name, Height)
VALUES ('AdamStart', '5')

Answer 1

You can use SQL UNION operator to do the same. Also, (Abs(Checksum(NewId())) % 10) generates a GUI internally, calculates the absolute value of its checksum. It will always generate a value between 0 and 10. Added 1 to handle the zero value case.

SELECT Name, Height FROM tbl
UNION
SELECT Name + 'Start', (Abs(Checksum(NewId())) % 10) + 1 FROM tbl