Is there an easier way to fill a column in SQL Server 2008?

I'm cr开发者_运维技巧eating a temp table and I want to fill the column from 0-9999 using T-SQL.

create table #tmp5 (userID int)

Is there an easier way to fill a column up instead of using a loop?

There are several approaches detaild here.

Apart from looping (with and without IDENTITY), creative use of CTEs, ROW_NUMBER and more.

Using GO N --N=number of iterations

CREATE TABLE #tmp5 (userID int IDENTITY(0, 1))
Go
INSERT INTO  #tmp5 DEFAULT VALUES
Go 10000

Or this:

CREATE TABLE #tmp5 (userID int)   
GO
INSERT INTO  #tmp5 VALUES (0)
GO
DECLARE @N int
SELECT @N=COUNT(*) FROM #tmp5
INSERT INTO  #tmp5 
SELECT userID+@N FROM #tmp5 WHERE userID+@N<10000
Go 14

use AdventureWorks --Or any database with a sizable amount of objects in it
go

insert into #tmp5 (userID)
select top 9999
  row_number() over (order by (select null)) as UserID
from sys.objects a
cross join sys.objects b 

This basically row_numbers over all the system objects, and the cross join ensures that you have a whole bunch of them to row number over. Depending on the size of the table you want to create and/or the size of your sys.objects table, you will need more or fewer cross joins.