generate fixed number of rows in a table

Question

Not able to word the question properly, so couldn't search what I want. All I need is a dummy table with a single column of say guids, which I use it for some other purposes. Without actually writing same insert .. newID() n times, wondering if there is an elegant solution.

Similar question would be how do I populate a blank table with a int column with say 1-n numbers.

Row1: 1
Row2: 2
.......
Row100:100

Answer 1

Instead of a recursive CTE, I recommend a set-based approach from any object you know already has more than 100 rows.

--INSERT dbo.newtable(ID, GUID)
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY [object_id]), NEWID()
  FROM sys.all_columns ORDER BY [object_id];

For plenty of other ideas, see this series:

• http://www.sqlperformance.com/generate-a-set-1
• http://www.sqlperformance.com/generate-a-set-2
• http://www.sqlperformance.com/generate-a-set-3