Make a loop inside an other one in SQL?

Question

I am working on PostGreSQL Version 1.18.1, and i am facing an issue :

I want to insert many informations and want to make it work like a loop, but for my request i need two loops (one inside the other)

my request look like this :

INSERT INTO table_hour (annee , mois , id_operateur , nombre_heure_sup , nombre_jour_recup )
SELECT 2017 , 2 , x.id , 0 , 0
FROM generate_series(1,6) AS  x(id);

INSERT INTO table_hour (annee , mois , id_operateur , nombre_heure_sup , nombre_jour_recup )
SELECT 2017 , 3 , x.id , 0 , 0
FROM generate_series(1,6) AS  x(id); 

...

First Loop : id_operateur From 1 to 6.

Second Loop : mois From 2 to 1000.

I want to knwo how to make it one inside the other (it's the value mois how will be the second loop)

I recommend you learn PL/pgSQL postgresql.org/docs/9.6/static/plpgsql.html and you will be able to loop whatever you want — nimdil
– nimdil, Commented Jun 20, 2017 at 8:45
I dont see how your two insert could be nested - can you please elaborate post a little?.. and also please check your version with select version() — Vao Tsun
– Vao Tsun, Commented Jun 20, 2017 at 8:46

klin · Accepted Answer · 2017-06-20 08:51:24Z

2

Use cross join:

INSERT INTO table_hour(annee , mois , id_operateur , nombre_heure_sup , nombre_jour_recup )
SELECT 2017 , y.id , x.id , 0 , 0
FROM generate_series(1,6) AS  x(id)
CROSS JOIN generate_series(2,1000) AS  y(id);

answered Jun 20, 2017 at 8:51

klin

123k15 gold badges241 silver badges263 bronze badges

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

Collectives™ on Stack Overflow

Make a loop inside an other one in SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related