Bulk Insert in PostgresSql-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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)