I'm using Derby DB and trying to copy data from a table in one database to a table with the same table definition in another database.
What is the correct syntax to specify the database.scheme.table in Derby SQL?
I've tried using the following select statement
statement = "INSERT INTO CH003..MHALL..CH_COMPANY SELECT * FROM CH001..MHALL..CH_COMPANY WHERE COMPANY_NUMBER = '" + companyNumber + "'";
but get the error Caused by: java.sql.SQLException: Syntax error: Encountered "." at line 1, column 22.
Changing the code to
statement = "INSERT INTO CH003.MHALL.CH_COMPANY SELECT * FROM CH001.MHALL.CH_COMPANY WHERE COMPANY_NUMBER = '" + companyNumber + "'";
I get the error SQLSyntaxErrorException: Syntax error: Encountered "." at line 1, column 27.
Is it possible to do this in Derby? Do you know the correct syntax? Thank you for your help.
A single Derby JDBC connection is connected to exactly one Derby database, so this is not possible in the way that you have attempted it.
However, there are other methods that you could use.
For example, you could use SYSCS_UTIL.SYSCS_EXPORT_TABLE to export the data from the table into a CSV file, then you could open a connection to the target database and use the SYSCS_UTIL.SYSCS_IMPORT_DATA to import that data. See https://db.apache.org/derby/docs/10.11/ref/rrefexportproc.html and http://db.apache.org/derby/docs/10.11/ref/rrefimportdataproc.html.
Or, you could read that data into, say, a data structure in your Java program, like an ArrayList, and then open a connection to your target database and write it out.
Or, you could use a tool like Apache DdlUtils to copy the data from one table to another: http://db.apache.org/derby/integrate/db_ddlutils.html
I'm sure there are other possibilities, but since you didn't give a lot of background about the underlying goal you're trying to accomplish, I won't try to speculate about why one approach might be better or worse than another.