How to use a SQL for loop to insert rows into database?

44
votes

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?

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
sqlpostgresql
It'd help to know what version of PostgreSQL you're using. - OMG Ponies
You need to use a LOOP - see the examples: linuxtopia.org/online_books/database_guides/… - OMG Ponies
@OMG Ponies Ouch thanks for the input. I don't suppose you could provide any pointers on the general idea of the syntax I'll need? I was looking at the loop documentation originally before posting this, which is how I ended up with that pseudo-code that I wrote in above, but I can't seem to get it into any sort of shape that Postgres seems to understand. - William Jones
@William Jones: The link provides PostgreSQL syntax - your FOR loop syntax is identical. - OMG Ponies
Also, doing this is straight forward(albeit not that efficient) using a shell and the psql command line tool. Are you on a *nix ? - nos

3 Answers

72
votes

Hopefully I've understood what you need (tested on 8.2):

INSERT INTO articles (id, name)
SELECT x.id, 'article #' || x.id
  FROM generate_series(1,10000000) AS x(id);
14
votes

In SQL Server you can do:

DECLARE @i int
SET @i = 1

WHILE @i<1000000
    BEGIN
        INSERT INTO articles
        VALUES @i
        SET @i=@i+1
    END
4
votes

Afaik, you can't write a loop directly as SQL, you'd have to create a stored procedure to do it.

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;