SQL: insert multiple rows at once

user_2829540 0
Viewed 1655

I have an array of values like this (restaurant_id is not a primary key):

[
{restaurant_id:1, day_of_week:0, from_time: "12:00", to_time: "14:00", is_open:false },
{restaurant_id:1, day_of_week:1, from_time: "12:00", to_time: "14:00", is_open:true },
{restaurant_id:1, day_of_week:2, from_time: "12:00", to_time: "14:00", is_open:true },
...
]

One entry for every day.

I would like to save each of them as a new row in a PostgreSQL database.

I have this query for one insert:

INSERT INTO schedules (restaurant_id, day_of_week, from_time, to_time, is_open) VALUES ($1, $2, $3, $4, $5) RETURNING schedules;

Should I do 7 INSERT statements or can I loop and save all in one statement?

What would be the query with the loop?

EDIT:

So I could do in one query something like this, as suggested:

 VALUES (?, ?, ?, ? ?),
        (?, ?, ?, ? ?),
        (?, ?, ?, ? ?),
        (?, ?, ?, ? ?),
        (?, ?, ?, ? ?),
        (?, ?, ?, ? ?),
        (?, ?, ?, ? ?)

but is there a better way of doing this?

postgresql sql
5 Answers

If all other values are constant(or derivable from the running variable), you can use generate_series()

INSERT INTO schedules (restaurant_id, day_of_week
           , from_time, to_time, is_open) 
SELECT 1, gs, '10:00','22:00', True
FROM generate_series(0,6) gs
        ;

Docs for generate_series https://www.postgresql.org/docs/11/functions-srf.html

user_9059020

You can issue one insert. I would recommend using parameters:

INSERT INTO schedules (restaurant_id, day_of_week, from_time, to_time, is_open)
    VALUES (?, ?, ?, ? ?),
           (?, ?, ?, ? ?),
           . . .
    RETURNING *;
user_11440350

INSERT INTO USERS(id,name,age) VALUES (1, 'Talha', 22), (2, 'John', 41), (3, 'William', 32);

This will work i guess.

user_104170580

If those input values are actually part of a JSON array, you can use that directly:

INSERT INTO schedules (restaurant_id, day_of_week, from_time, to_time, is_open) 
select (v ->> 'restaurant_id')::int, 
       (v ->> 'day_of_week')::int,
       (v ->> 'from_time')::time,
       (v ->> 'to_time')::time,
       (v ->> 'is_open')::boolean
from jsonb_array_elements('
[
  {"restaurant_id":1, "day_of_week":0, "from_time": "12:00", "to_time": "14:00", "is_open":"false" },
  {"restaurant_id":1, "day_of_week":1, "from_time": "12:00", "to_time": "14:00", "is_open":"true" },
  {"restaurant_id":1, "day_of_week":2, "from_time": "12:00", "to_time": "14:00", "is_open":"true" }
]'::jsonb) as t(v);

Of course you need to replace the hardcoded string value with a proper parameter, e.g. from jsonb_array_elements(cast(? as jsonb))

user_3303150

Kevin Amiranoff, You can use stored procedures of mysql for that.

begin for i in 1 .. 100000 loop insert into users values ( i, 'Talha' ,20+i); end loop; commit; end;

user_104170580
Related