Bulk Insert in PostgresSql

user_16928074 0
Viewed 46

Hi I have this simple insert statement

insert into location_access (employee,location,permissionid)
values
(empID,8241,4),
(empID,8260,4),
(empID,8269,4),
(empID,8261,4),
(empID,8170,4),
(empID,7908,4),
(empID,8008,4),
(empID,8026,4),
(empID,8029,4),
(empID,8242,4),
(empID,8270,4),
(empID,8278,4),
(empID,8279,4),
(empID,8281,4),
(empID,8284,4),
(empID,8288,4),
(empID,8171,4),
(empID,7909,4),
(empID,8156,4),
(empID,7258,4),
(empID,8027,4)

Also for the empiD I have a list of over 190 employeeIDs so hardcoding the values would be a pain is there a better way to do this maybe a "loop" to cycle all the employee ids and add them to each location?

sample employeeIDs

41743
41746
41753
43554
43557
43561
43563
43568
43569
43570
43572
43573
43574
43576
43577
43579
43582
43584
sql-insert insert postgresql sql
2 Answers

You can cross join against a list of employee IDs:

insert into location_access (employee,location,permissionid)
select source.employee, source.location, source.permissionid
from (
 values
   (41743),(41746),(41753),(43554),(43557),(43561),
   (43563),(43568),(43569),(43570),(43572),(43573),
   (43574),(43576),(43577),(43579),(43582),(43584)
) as emp(empid) 
  cross join lateral (
    values 
      (emp.empid,8241,4),
      (emp.empid,8260,4),
      (emp.empid,8269,4),
      (emp.empid,8261,4),
      (emp.empid,8170,4),
      (emp.empid,7908,4),
      (emp.empid,8008,4),
      (emp.empid,8026,4),
      (emp.empid,8029,4),
      (emp.empid,8242,4),
      (emp.empid,8270,4),
      (emp.empid,8278,4),
      (emp.empid,8279,4),
      (emp.empid,8281,4),
      (emp.empid,8284,4),
      (emp.empid,8288,4),
      (emp.empid,8171,4),
      (emp.empid,7909,4),
      (emp.empid,8156,4),
      (emp.empid,7258,4),
      (emp.empid,8027,4)
   ) as source(employee,location,permissionid)
user_3303150

With For loop, multiple records could be inserted

DO
$do$
DECLARE
   a integer[] := array[41743,41746,41753,43554,43557,43561,43582,43584 ];
   i integer;                   
BEGIN
   FOR i IN 1 .. array_upper(a, 1)
   LOOP
      RAISE NOTICE '%', a[i];      -- single quotes
      insert into location_access (employee,location,permissionid)
values
(a[i],8241,4);
   END LOOP;
END
$do$;

Here is the demo https://extendsclass.com/postgresql/ecb3b7c

user_33738540
Related