1

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

JAL
  • 41,701
  • 23
  • 172
  • 300
oyers
  • 35
  • 6
  • Derby has several ways to export table data: https://db.apache.org/derby/docs/10.12/adminguide/cadminimport16245.html – Bryan Pendleton Oct 27 '15 at 00:43
  • I have tried exporting tables with sys_utils in Derby, but it doesnot really convert it to sql statements. I exported it to .csv format which has a table format. – oyers Oct 27 '15 at 13:54
  • True. If it's mandatory for your project that the data be exported in SQL insert statements, you'll have to use a separate DBMS tool, as Derby does not provide such a tool built-in. – Bryan Pendleton Oct 27 '15 at 17:58
  • But I want that every time when derby database is created , corresponding sql statements are also generated. Is this possible? As far as I know, other tools can only export it once or we will have to do it every time the db is created. – oyers Oct 27 '15 at 20:44

2 Answers2

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)

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
0
  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.

James
  • 1