Example loading a fixed-text file to PostgreSQL with a Stored Procedure.

"Stored Procedures are evil", "Stored Procedures do break database independency", "Do not use Stored Procedure in my enterprise".

This are some of words I hear about SPs. But you can do simple loads of data quickly.

With Stored Procedures, you can load big data quickly, (and make very fast systems also).
I will use PostgreSQL, which is a DB I can use for this.


Let's see a file...
0000023ABRAAO                                                           09081985
0000045JOSEPH                                                           10071997
...


The format table is:
create table employee (
ID  NUMER(7)
NAME CHAR(100)
HIRE_DATE DATE   --(MMDDYYYY) in file
);

A log table is:
create table log_errors (
id bigserial,
table_name varchar(100),
error_date timestamp,
line text,
message varchar(200),

constraint log_errors_pk primary key(id)
);


A staging table for the file (we'll COPY file into this):
create table temp_employee
   file_line text,
   id bigserial,
   constraint  tepk primery key(id)
);





Let's divide the SP in parts.
Part 1: Header.

CREATE OR REPLACE FUNCTION load_employees()
  RETURNS boolean AS
$BODY$declare
    line text;
    emp employee%ROWTYPE;
begin



Part 2: Loading the data to the real table. We'll make it by the complex way: update or insert and log the bad records into log_errors.


 for line in select file_line from temp_employee order by id
 loop begin
        emp:=null;
        emp.name:=trim(substr(line,9, 100));
        emp.hire_date:=to_date(substr(line,110, 8), 'MMDDYYYY');
        emp.id:=substr(line, 1, 8);


        update employee set 
           name = emp.name,
           hire_date = emp.hire_date
            where id = emp.id;


        if not found then
            insert into employee(id, name, hire_date) values
            (emp.id, emp.name, emp.hire_date);
       end if;
       
 exception
      when others then
            insert into log_erros (table_name, error_date, line, message)
             values ('employee', now(), line, 'Error'); 
  end;
 end loop;



Part 3: Cleaning records from temp_employee.
delete from temp_employee;



Part 4: SP Footer.


   return true; 
end;$BODY$
  LANGUAGE plpgsql VOLATILE


Part 5: Call the function from psql.


bash-4.2$ cat /tmp/employee.txt|psql -h thehost db -c "copy temp_employee from stdin;select load_employees()"


If you need to run it automatically, remember to put the "Part 5" in the crond service. For Windows server, use the Task Scheduler.