Is there any way to convert derby database table rows to SQL insert statements

Question

I have successfully exported the Derby schema using ddlUtils but how do I export table data in derby to insert SQL statements?

Answer 1

If you're willing to use a third-party tool for this, you could use jOOQ

public class ExportAsInsert {
    public static void main(String[] args) {
        try (DSLContext ctx = DSL.using(url, user, password)) {
            ctx.meta()
               .getSchemas()
               .stream()

               // Filter out those schemas that you want to export
               .filter(schema -> schema.getName().equals("TEST"))

               // Get the tables for each schema...
               .flatMap(schema -> schema.getTables().stream())

               // ... and format their content as INSERT statements.
               .forEach(table -> System.out.println(ctx.fetch(table).formatInsert()));
        }
    }
}

There's a known issue that the above generates the wrong table name in the insert statement. You can fix this by patching the output:

System.out.println(
    ctx.fetch(table).formatInsert().replace("UNKNOWN_TABLE", table.getName()));

(Disclaimer: I work for the company behind jOOQ)

Answer 2

1. Run your SQL request like "select * from schema.table;".
2. In the window where the rows are displayed, select them all with keyboard, not "control+A" which has no effect.
3. Right-click and in the contextual menu, select "Show SQL Script for INSERT" : a pop-up window in displayed, and you just have to copy the content to a text file.

Note : I use Netbeans 8.2.