如何使用 SQL for 循环将行插入数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3764001/
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
How to use a SQL for loop to insert rows into database?
提问by William Jones
I'm using Postgres, and I have a large number of rows that need to be inserted into the database, that differ only in terms of an integer that is incremented. Forgive what may be a silly question, but I'm not much of a database guru. Is it possible to directly enter a SQL query that will use a loop to programatically insert the rows?
我正在使用 Postgres，并且我有大量的行需要插入到数据库中，这些行仅在递增的整数方面有所不同。原谅一个愚蠢的问题，但我不是一个数据库专家。 是否可以直接输入将使用循环以编程方式插入行的 SQL 查询？
Example in pseudo-code of what I'm trying to do:
我正在尝试做的伪代码示例：
for i in 1..10000000 LOOP
INSERT INTO articles VALUES(i)
end loop;
回答by Milen A. Radev
Hopefully I've understood what you need (tested on 8.2):
希望我已经了解您的需要（在 8.2 上测试）：
INSERT INTO articles (id, name)
SELECT x.id, 'article #' || x.id
FROM generate_series(1,10000000) AS x(id);
回答by JNK
In SQL Server you can do:
在 SQL Server 中，您可以执行以下操作：
DECLARE @i int
SET @i = 1
WHILE @i<1000000
BEGIN
INSERT INTO articles
VALUES @i
SET @i=@i+1
END
回答by nos
Afaik, you can't write a loop directly as SQL, you'd have to create a stored procedureto do it.
Afaik，您不能直接将循环编写为 SQL，您必须创建一个存储过程来执行此操作。
This will do though (but someone can probably make it cleaner)
这虽然可以（但有人可能会使它更干净）
INSERT INTO articles WITH RECURSIVE i AS
(
SELECT 1 x
UNION ALL
SELECT x + 1
FROM i
WHERE x < 10000000
)
SELECT x
FROM i;
