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

Question

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;

You need to use a LOOP - see the examples: linuxtopia.org/online_books/database_guides/… — OMG Ponies
– OMG Ponies, Commented Sep 21, 2010 at 21:28
@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, Commented Sep 21, 2010 at 21:30
@William Jones: The link provides PostgreSQL syntax - your FOR loop syntax is identical. — OMG Ponies
– OMG Ponies, Commented Sep 21, 2010 at 21:48
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
– nos, Commented Sep 21, 2010 at 22:15

vyegorov · Accepted Answer · 2012-05-17 20:08:07Z

76

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

edited May 17, 2012 at 20:08

vyegorov

23k7 gold badges61 silver badges76 bronze badges

answered Sep 21, 2010 at 22:53

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Korobko Alex Over a year ago

Thanks. I had to add a permission for all my roles, so I did: insert into ROLE_PERM (ROLE_ID, PERMISSION_ID) select ROLE_ID, 722 from ROLE order by ROLE_ID;

Alkanshel Over a year ago

The most concise answer I've seen!

JNK · Accepted Answer · 2010-09-21 20:26:44Z

14

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

answered Sep 21, 2010 at 20:26

JNK

65.5k15 gold badges127 silver badges138 bronze badges

2 Comments

JNK Over a year ago

Someone like OMGPonies can tell us if this will work in postgre

OMG Ponies Over a year ago

My pgPLSQL is weak, but: linuxtopia.org/online_books/database_guides/… I'd use the recursive WITH if possible (like you see in nos' answer), but I don't have an instance to test so it looks like a loop is required (for prior to 8.4, at a minimum). But with PostgreSQL v9 adding anonymous pgPLSQL blocks (finally, dunno how long Oracle supported), it's easier for one time use.

nos · Accepted Answer · 2010-09-21 20:20:14Z

4

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;

edited Sep 21, 2010 at 20:20

answered Sep 21, 2010 at 20:13

nos

231k60 gold badges436 silver badges516 bronze badges

2 Comments

OMG Ponies Over a year ago

The recursive WITH is 8.4+, but there's nothing in the documentation about supporting it in an INSERT statement. That's not necessarily definitive...

OMG Ponies Over a year ago

OP confirmed using v8.3, can't use recursive WITH :(

Nick · Accepted Answer · 2023-01-27 11:11:56Z

1

To people still visiting and want proper answer in the author's format, here is the answer.

do $$
begin
for i in 1..1000000 loop
insert into articles values(i);
end loop;
end;
$$;

answered Jan 27, 2023 at 11:11

Nick

113 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related