Dataload utility to load custom table

Often you may come across requirements to load custom data from an external data feed into a custom WCS table, OOB Dataload utility provides a Mediator called "TableObjectMediator" which can be used for such purpose, This technique does not require any coding and can be quickly setup by defining configuration files to map data feed with custom table.

This example also demonstrates loading of system generated and custom unique key from an external CSV file, any new records will be inserted and existing records (based on primary and unique) keys will be updated.

Step 1

Create a custom table, in this example my primary key is system generated and we will use IDResolver for this purpose, all other data will be fed from the input CSV file.

----- Define custom_table_derby.sql with following content -----

connect '..\db\mall;create=true'; 

CREATE TABLE XMYTable (
MY_SYSTEM_ID BIGINT NOT NULL,
MY_ID BIGINT NOT NULL,
firstName VARCHAR(254),
lastName VARCHAR(254)
);

ALTER TABLE XMYTable
       ADD PRIMARY KEY (MY_SYSTEM_ID);
       
CREATE UNIQUE INDEX I0000_XMYTable ON XMYTable
(
       MY_ID     ASC
);

INSERT INTO KEYS (KEYS_ID, TABLENAME, COLUMNNAME, COUNTER, PREFETCHSIZE, LOWERBOUND, UPPERBOUND, OPTCOUNTER)
VALUES ((SELECT MAX(KEYS_ID) + 1 FROM KEYS), 'xmytable', 'MY_SYSTEM_ID', 0000, 1, 1000, 999999, 1);

commit;

------------------------------------------

Step 6: