GSquared (12/27/2010)
The first part is easy. It can be done in one Insert...Select if you just use the Row_Number() function.
IF OBJECT_ID(N'tempdb..#Numbers') IS NOT NULL
DROP TABLE #Numbers;
CREATE TABLE #Numbers (Number INT PRIMARY KEY);
INSERT INTO #Numbers (Number)
SELECT TOP 60000 ROW_NUMBER() OVER (ORDER BY T1.OBJECT_ID)
FROM sys.all_columns AS T1
CROSS JOIN sys.all_columns AS T2;
Keep adding references to the system view till you get at least 60k rows. (Could be one instance, could be more. Will depend on the database you're doing this in.)[/code]
If you change the code to use the master.sys.all_columns, there is (thru SQL 2008R2) > 5000 rows in this view. When cross-joined to itself one time, this gives > 25 million rows.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes