如何使用SQL for循环将行插入数据库?

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;

3 个解决方案

#1

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);

#2

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

#3

Afaik, you can't write a loop directly as SQL, you'd have to create a stored procedure to 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;

#1