Inserting n number of records with T-SQL

sql, sql-server, sql-server-2000, t-sql

Solution

this is the approach I'm using and works best for my purposes and using SQL 2000. Because in my case is inside an UDF, I can't use ## or # temporary tables so I use a table variable. I'm doing:

DECLARE @tblRows TABLE (pos int identity(0,1), num int) 
DECLARE @numRows int,@i int


SET @numRows = DATEDIFF(dd,@start,@end) + 1
SET @i=1

WHILE @i<@numRows
begin
    INSERT @tblRows SELECT TOP 1 1 FROM sysobjects a

    SET @i=@i+1
end

Problem

I want to add a variable number of records in a table (days) And I've seen a neat solution for this: ``` SET @nRecords=DATEDIFF(d,'2009-01-01',getdate()) SET ROWCOUNT @nRecords INSERT int(identity,0,1) INTO #temp FROM sysobjects a,sysobjects b SET ROWCOUNT 0 ``` But sadly that doesn't work in a UDF (because the #temp and the SET ROWCOUNT). Any idea how this could be achieved? At the moment I'm doing it with a WHILE and a table variable, but in terms of performance it's not a good solution.

Original source