views:

395

answers:

8

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.

A: 

You could do what PinalDave suggests:

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO
p.campbell
but I need to add n rows... it could be 2 it could be 2,000! That could work building a dynamic query still using the while loop though and then do the insert at the end, but it wouldn't work in a UDF anywyay.
tricat
Please don't quote PinalDave...
gbn
A: 

you can use a cross join

select top 100000 row_number() over(order by t1.number)-- here you can change 100000 to a number you want or a variable
from   master.dbo.spt_values t1
       cross join master.dbo.spt_values t2
Mladen Prajdic
+1  A: 

You can use a WHILE statement for that:

declare @i int
declare @rows_to_insert int
set @i = 0
set @rows_to_insert = 1000

while @i < @rows_to_insert
    begin
    INSERT INTO #temp VALUES (@i)
    set @i = @i + 1
    end
Andomar
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 as shown in my answer
tricat
+3  A: 

If you're using SQL 2005 or newer, you can use a recursive CTE to get a list of dates or numbers...

with MyCte AS
    (select   MyCounter = 0
     UNION ALL
     SELECT   MyCounter + 1
     FROM     MyCte
     where    MyCounter < DATEDIFF(d,'2009-01-01',getdate()))
select MyCounter, DATEADD(d, MyCounter, '2009-01-01')
from   MyCte 
option (maxrecursion 0)


/* output...
MyCounter   MyDate
----------- -----------------------
0           2009-01-01 00:00:00.000
1           2009-01-02 00:00:00.000
2           2009-01-03 00:00:00.000
3           2009-01-04 00:00:00.000
4           2009-01-05 00:00:00.000
5           2009-01-06 00:00:00.000
....
170         2009-06-20 00:00:00.000
171         2009-06-21 00:00:00.000
172         2009-06-22 00:00:00.000
173         2009-06-23 00:00:00.000
174         2009-06-24 00:00:00.000

(175 row(s) affected)

*/
Scott Ivey
I think this approach should be limited to relatively small result sets like 1,000 or less. The recursive operations start getting visibly expensive for even 100,000 results. However, it has still got to be the neatest thing on here.
SurroundedByFish
+2  A: 

When you have a pre-built numbers table, just use that:

SELECT *
FROM numbers
WHERE number <= DATEDIFF(d,'2009-01-01',getdate())

There are any number of techniques for building the numbers table in the first place (using techniques here), but once it's built and indexed, you don't build it again.

Cade Roux
Most people, especially from coder backgrounds, would laugh at the idea of having a table that's just filled with a million or more sequential numbers. However, I think this is actually the best solution on here. That's to say, it is the solution that breaks the intention of set-based operations the least and isn't a hack. Coders think in iteration and recursion, but they have trouble comprehending set-based operations. The "numbers" table is going to take up a constant, relatively small amount of space and return extremely fast results even for massive result sets. But hey, it isn't sexy.
SurroundedByFish
Whether you build it on the fly and however you build it, it's still a numbers table (even if it's disguised as a series of dates).
Cade Roux
A: 

How about:

DECLARE @nRecords INT

SET @nRecords=DATEDIFF(d,'2009-01-01',getdate())

SELECT TOP (@nRecords)
    ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id) - 1
FROM sys.objects a, sys.objects b

If you don't want it zero-indexed, remove the " - 1"

Requires at least SQL Server 2005.

GalacticCowboy
sorry, forgot to mention I'm on SQL 2000
tricat
In that case, you're *severely* limited in what you can do, and almost none of the solutions suggested here will work. Cade Roux's is probably your best bet.
GalacticCowboy
A: 

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
tricat
A: 

Overall much faster to double the amount of rows at every iteration

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY)
GO
DECLARE @i INT;
SET @i = 1;
INSERT INTO dbo.Numbers(n) SELECT 1;
WHILE @i<128000 BEGIN
  INSERT INTO dbo.Numbers(n)
    SELECT n + @i FROM dbo.Numbers;
  SET @i = @i * 2;
END;

I deliberately did not SET NOCOUNT ON, so that you see how it inserts 1,2,4,8 rows

AlexKuznetsov