SQLULDR2 :: Tips 4 – Generate Insert SQL for MySQL and Oracle

It’s quite good for large tables in text format file, insert SQLs are prefered for small tables to skip the writing of data load control file, such as Oracle sqlldr control file. You can do it quickly with SQLULDR2.

Let’s generate some insert SQLs for MySQL databases by set “text” option as “mysqlins”, all the non-digital column value will be dumped as hex string with a “unhex” function call.

$ sqluldr2 test/test query=tab table=tab text=mysqlins file=-
INSERT  INTO `tab` (`TNAME`,`TABTYPE`,`CLUSTERID`) VALUES
(unhex('4143545f52555f5441534b'),unhex('5441424c45'),null),
(unhex('485049534c454d'),unhex('5441424c45'),null),
(unhex('5445535431'),unhex('5441424c45'),null),
(unhex('545f54455354'),unhex('5441424c45'),null),
(unhex('545f5445535432'),unhex('5441424c45'),null),
(unhex('5649455754455354'),unhex('5441424c45'),null);

You can set the target table name by “table” option, but the column name cannot be adjusted, you can change the column alias in the  SQL query. Set “text” option as “oracleins” to generate insert SQLs for Oracle database, each row will be a single insert SQL.

$ sqluldr2 test/test query=tab text=oracleins file=-
INSERT  INTO tab (TNAME,TABTYPE,CLUSTERID) VALUES
('ACT_RU_TASK','TABLE',null);
INSERT  INTO tab (TNAME,TABTYPE,CLUSTERID) VALUES
('HPISLEM','TABLE',null);
INSERT  INTO tab (TNAME,TABTYPE,CLUSTERID) VALUES
('TEST1','TABLE',null);
INSERT  INTO tab (TNAME,TABTYPE,CLUSTERID) VALUES
('T_TEST','TABLE',null);
INSERT  INTO tab (TNAME,TABTYPE,CLUSTERID) VALUES
('T_TEST2','TABLE',null);
INSERT  INTO tab (TNAME,TABTYPE,CLUSTERID) VALUES
('VIEWTEST','TABLE',null);

For MySQL  insert SQLs, there is an “on duplicate update” section, you can control it with “unique” option (specify the key columns) and “update” option (whether or not to enable this feature).

$ sqluldr2 test/test query=tab text=mysqlins \
  unique=TNAME update=on file=- 
INSERT  INTO `tab` (`TNAME`,`TABTYPE`,`CLUSTERID`) VALUES
(unhex('4143545f52555f5441534b'),unhex('5441424c45'),null),
(unhex('485049534c454d'),unhex('5441424c45'),null),
(unhex('5445535431'),unhex('5441424c45'),null),
(unhex('545f54455354'),unhex('5441424c45'),null),
(unhex('545f5445535432'),unhex('5441424c45'),null),
(unhex('5649455754455354'),unhex('5441424c45'),null)
ON DUPLICATE KEY UPDATE
    `TABTYPE`=VALUES(`TABTYPE`), `CLUSTERID`=VALUES(`CLUSTERID`);

Some time it can make you life better by getting work done more quickly.