paul.knibbs (8/8/2014)

---

Jeff Moden (8/6/2014)

Actually, that's one of the questions that I frequently ask except it's to a million and the results have to be stored in a table.

I actually sat down to figure out how I'd do this, and I came up with the following:

INSERT INTO #test SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY A.name) AS num FROM master.dbo.spt_values AS A CROSS JOIN master.dbo.spt_values AS B

However, while figuring this out, I had to look up the syntax for ROW_NUMBER(), I forgot that the two tables in the CROSS JOIN had to be aliased if they were identical, and I then had a fun time figuring out why it *still* wouldn't work--it's because I'd enclosed the SELECT in brackets when it shouldn't be! I can only offer as a mitigating factor that I'm a general server admin and don't deal with SQL stuff daily, but I thought I knew T-SQL a bit better than that.