Let me give you an example by simplifying your code.

DECLARE @id INT

SELECT @id= max(id) + 1

FROM TABLE

INSERT INTO TABLE (

id

,country

)

SELECT

n

,'Germany'

FROM dbo.Tally

WHERE n BETWEEN @id AND @id <= 1002261

You wouldn't need a cycle or an explicit transaction because it's only one statement. And if the ID is defined as an Identity column, you need even less code.

If you don't like the idea of using a physical table and want everything done on memory, you could create a view for a cteTally with schemabinding as suggested by Sean Lange which I could search for you if you want.

8K Splitter is very common for unnormalized data.

Finally, to understand CROSS/OUTER APPLY, think of it as a JOIN with extended functionality. More info on http://www.sqlservercentral.com/blogs/sqlstudies/2013/05/20/the-many-uses-of-cross-apply/