TrImport – Trim your insert statements

Did you ever want to copy data from database to another and want to exclude certain columns? With the basic export utility in SQL Developer one can specify a selection criteria (effectively a where clause) to export certain rows. It will produce an SQL script with insert statements. However, virtual, or calculated, columns are also included, even though their data can be derived. This is a problem only when attempting to run the script against the target database, it will not allow you to perform an insert on a virtual column. It is my understanding that this is to be addressed in a later Oracle Database 11g release. Until then, what are you to do?

Well, you could use Oracle Data Pump to export only the specific columns. However, in my case, I had the export SQL scripts to start with, provided my someone else, and not the original DB.

I put together a very simple Java Swing application (called TrImport) which allows you to remove columns, and their corresponding values, from an insert statement in an SQL script.

With TrImport now you can easily change the following SQL statement

insert into TABLE_A set (ROW_ID, NAME, DESC, NAME_VC) values (456, ‘Record Two’, ‘This is a record’, ‘record two’);

to this

insert into TABLE_A set (ROW_ID, NAME, DESC) values (456, ‘Record Two’, ‘This is a record’);

with ease. Obviously the benefit is not that this can be done with just one insert statement, but all of the insert statements in a file.

The steps are really simple:
1. Open the file with you SQL script. This can be a mixed script with inserts, updates, comments, just about anything.
2. Open the columns window to see the tables and columns that TrImport has identified in any insert statements it has found.
3. Tick the columns you want to exclude and press OK.
4. Save the file to a new location.

TrImport is not just for virtual columns either, it can also be used in a situation where the table in the destination database has fewer columns.

To get TrImport and run it simply download the trimport-0.1.jar and from the command line execute ‘java -jar trimport-0.1.jar’. If you wanted to, you could setup a short cut. TrImport is provided without any support. It has only been tested with a small set of SQL scripts and only with one function, that is to_timestamp.