使用T-SQL插入n个记录

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?

但遗憾的是，这在UDF中不起作用（因为#temp和SET ROWCOUNT）。知道如何实现这一目标吗？

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.

目前我正在使用WHILE和表变量，但就性能而言，这不是一个好的解决方案。

8 个解决方案

#1

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

如果您使用的是SQL 2005或更新版本，则可以使用递归CTE获取日期或数字列表...

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)

*/

#2

You can use a WHILE statement for that:

您可以使用WHILE语句：

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

#3

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:

这是我正在使用的方法，最适合我的目的并使用SQL 2000.因为在我的情况下是在UDF中，我不能使用##或＃临时表，所以我使用表变量。我正在做：

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

#4

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

我故意没有SET NOCOUNT ON，所以你看它如何插入1,2,4,8行

#5

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

#6

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.

有许多用于构建数字表的技术（使用此处的技术），但是一旦构建并编制索引，就不会再次构建它。

#7

You could do what PinalDave suggests:

你可以做PinalDave建议的事情：

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

#8

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"

如果您不希望零索引，请删除“ - 1”

Requires at least SQL Server 2005.

至少需要SQL Server 2005。

#1