使用 T-SQL 插入 n 条记录

Question

我想在表中添加可变数量的记录（天）

并且我已经看到了一个巧妙的解决方案：

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

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

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

Answer 1

当您有预先构建的数字表时，只需使用它：

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

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

Answer 2

这是我正在使用的方法，并且最适合我的目的并使用 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

Answer 3

总体而言，每次迭代时将行数加倍要快得多，

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; 

我故意没有设置 NOCOUNT ON，以便您看到它如何插入 1,2,4,8 行

Answer 4

你可以做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

Answer 5

怎么样：

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

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

至少需要 SQL Server 2005。

Answer 6

如果您使用的是 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)

*/

Answer 7

您可以使用 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

Answer 8

你可以使用交叉连接

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