使用 T-SQL 插入 n 条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1041163/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting n number of records with T-SQL
提问by David Aleu
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 和表变量来做，但就性能而言，这不是一个好的解决方案。
采纳答案by David Aleu
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
回答by Scott Ivey
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)
*/
回答by Andomar
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
回答by Cade Roux
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.
有许多技术可以首先构建数字表（使用此处的技术），但是一旦构建并索引，您就不会再次构建它。
回答by A-K
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 行的
回答by Mladen Prajdic
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
回答by p.campbell
You could do what PinalDavesuggests:
您可以按照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
回答by GalacticCowboy
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。
