How to copy a table from another database?

[Wayne Johnson]

Upon converting from ODBC and MS Access Driver, I now cannot copy a table from a source database with the following SQL:

INSERT INTO TestInfo SELECT * FROM [ C:\Data\P81\LCAP.MDB ].[TestInfo]


The result is an exception: "invalid schema name: C__DATA_P81_LCAP_MDB"

How can I do this in UCanAccess/hsqldb?

[Marco Amadei]

If you aren't accessing to a second database you have just to establish another connection.
Also, if you want to use the same connection, you have to import the TestInfo table in the first database as linked table.
If it's the same database of your connection, you shouldn't use the schema name.

[Wayne Johnson]

I didn't give you the full story. I have created a new database by copying a default with some constant tables and some empty tables to be filled in. Then I connect to the new db and begin to copy from an old (source) database into the new one to which I have established a connection in PocDatabase.connection. Below is one of the copy methods.

 // copy table from a source db into current db
 public static void copy() {
   String source = PocDatabase.sourceDbfile;
   // attempt to copy whole table
   System.out.println("TestInfoDbTable.copy: from: " + source);
   try ( Statement statement = PocDatabase.connection.createStatement();)
   {
     String query = "INSERT INTO TestInfo " +
                    "SELECT * FROM [" + source + "].[TestInfo]";
     statement.execute(query);
   } catch (SQLException ex1) {
     System.err.println("TestInfoDbTable.copy: Exception: " + ex1);
   }
 }

So, the variable source contains the location of the source database from which I want to copy the TestInfo table into the currently connected database.

I don't understand your statement "import the TestInfo table in the first database as linked table". What is a linked table?

Thanks for your patience and help.

[Gord Thompson]

Hi Wayne.

A linked table is a "virtual table" in an Access database file that points to a real table in another database file. For example, you can have a table named "TableA" in A.accdb, and you can create a linked table in B.accdb that points to that same table. Then in B.accdb you can refer to the linked table just like it was native to that file, but the information is actually retrieved from (and updates written to) the "real" table in A.accdb.

For more information, see the Microsoft Support article here.

[Wayne Johnson]

Back on this subject, I need to be able to copy a complete table from another database. (A link will not do.) Is there an SQL statement that can do that, like the example I posted above? Or do I have to read from the source into a result set and then update the target database column by column and row by row (tedious)? Is there a document or website that defines the SQL standard used by this tool?

[Marco Amadei]

UCanaccess can't see database paths as schema whilst Access does it.
You can either follow the tedious approach above or an exciting mixed approach:
-get the ResultSet from the first database through UCanAccess
-get a jackcess connection to second database Database db = DatabaseBuilder.open(new File("my.mdb"));
-use the com.healthmarketscience.jackcess.ImportUtil method public static String importResultSet(ResultSet source,
Database db,
String name,
ImportFilter filter,
boolean useExistingTable)
throws SQLException,
IOException

-Please, let me know your findings: never tried.

[Gord Thompson]

Hi Marco. FWIW, I just tried the following code to copy a simple table

ID - Long Integer
TextField
DateField
MemoField
YesNoField

and it seems to have worked fine.

package ucanCopyTable;

import java.io.*;
import java.sql.*;
import com.healthmarketscience.jackcess.*;
import com.healthmarketscience.jackcess.util.ImportUtil;

public class UCanCopyTable {

    public static void main(String[] args) {
        // test data
        String sourcePath = "C:/Users/Public/test/sourceDb.accdb";
        String destinationPath = "C:/Users/Public/test/destinationDb.accdb";
        String tableName = "Table1";

        try (Connection conn = DriverManager.getConnection("jdbc:ucanaccess://" + sourcePath)) {
            try (Statement s = conn.createStatement()) {
                try (ResultSet rs = s.executeQuery("SELECT * FROM [" + tableName + "]")) {
                    // Jackcess calls:
                    Database db = DatabaseBuilder.open(new File(destinationPath));
                    ImportUtil.importResultSet(rs, db, tableName);  // create new table
                }
            }
        } catch (Exception e) {
            e.printStackTrace(System.err);
        }
    }

}

[Marco Amadei]

Ok, thank you!
For UcanAccess 3, I'm thinking if it may be possible to expose the access file in the same folder as schemas, as well as Access.
This would allow cross-database queries.

[Wayne Johnson]

I have finally succeeded copying one of my large, complex databases using the "tedious" method referred to above. Here is a sample:

    String query = "SELECT * FROM ACNav ";
    try ( Statement stmtout = PocDatabase.connection.createStatement(
            ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
          Statement stmtin = PocDatabase.sourceConnection.createStatement();
          ResultSet rs = stmtin.executeQuery(query);
    ) 
    {
      while (rs.next()) {
        ResultSet rsout = stmtout.executeQuery(query + "WHERE AutoID=0");
        rsout.moveToInsertRow();
        rsout.updateInt(1, 1);  // AutoID
        rsout.updateTimestamp(2, rs.getTimestamp(2));
        rsout.updateDouble(3, rs.getDouble(3));
        rsout.updateDouble(4, rs.getDouble(4));
        rsout.updateFloat(5, rs.getFloat(5));
        rsout.updateFloat(6, rs.getFloat(6));
        rsout.updateFloat(7, rs.getFloat(7));
        rsout.updateFloat(8, rs.getFloat(8));
        rsout.updateFloat(9, rs.getFloat(9));
        rsout.updateFloat(10, rs.getFloat(10));
        rsout.insertRow();
      }
    } catch (SQLException ex1) {
    }

It works, BUT...
It takes forever!

With Java 7 and "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}", using the INSERT INTO method cited in the original post, it took 9 seconds.

With Java 8 and "jdbc:ucanaccess://" it took 1655 seconds! That's unacceptable. What else can I do?

[Marco Amadei]

It looks like you're running in autocommit mode.
The use of a transaction instead,

connection.setAutocommit(false) before the loop
and
connection.commit() after the loop,

will drammatically improve the performance.
Should you face with memory issues (out of memory), just call connection.commit() each 5000/10000 records.
Please, let me know your results. Just a diamond is forever.

[Wayne Johnson]

Thanks again, Marco.

Implementing your suggestion brought the time down to 200 seconds -- a BIG improvement from 1600, but still a big increase over 9.

The initial try ran out of memory, so I implemented a counter and committed after 5000 rows. Then it achieved the 200 second result.

[Marco Amadei]

Well, we can still improve drammatically.
Don't you use a scrollable ResultSet but a batch. I hope that's funny for you(SEE THE FINAL NOTE!!!!):

try (

    PreparedStatement stmtout 
  =PocDatabase.connection.prepareStatement("insert into ACNav              values(?,?,?,?,?,?,?,?,?,?)"); 
      Statement stmtin = PocDatabase.sourceConnection.createStatement();
      ResultSet rs = stmtin.executeQuery(query);
) 
{
  while (rs.next()) {

      stmtout.setInt(1, 1);  // AutoID
      stmtout.setTimestamp(2, rs.getTimestamp(2));
      stmtout.setDouble(3, rs.getDouble(3));
      stmtout.setDouble(4, rs.getDouble(4));
      stmtout.setFloat(5, rs.getFloat(5));
      stmtout.setFloat(6, rs.getFloat(6));
      stmtout.setFloat(7, rs.getFloat(7));
      stmtout.setFloat(8, rs.getFloat(8));
      stmtout.setFloat(9, rs.getFloat(9));
      stmtout.setFloat(10, rs.getFloat(10));
      stmtout.addBatch();


  }

THE FINAL NOTE: JUST BEFORE EACH COMMIT: stmtout.executeBatch();
PS: So doing you can also commit each 20000 rows.
It should take about 20s.
Please, let me know your results.

[Gord Thompson]

I did a couple of quick tests with a 5-column table containing 10,000 rows. I found that if I created the table structure in the destination database first, then the following code took about 1,600 ms to run:

connStr = "jdbc:ucanaccess://C:/Users/Public/UCanAccessDemo.accdb";
try (Connection conn = DriverManager.getConnection(connStr)) {
    StopWatch sw = new StopWatch();
    sw.start();
    try (Connection conn2 = DriverManager.getConnection("jdbc:ucanaccess://C:/Users/Gord/Desktop/target.accdb")) {
        conn2.setAutoCommit(false);
        try (PreparedStatement ps = conn2.prepareStatement("INSERT INTO TestData10k (ID, TextField, LongIntegerField, DoubleField, Tasked) VALUES (?,?,?,?,?)")) {
            try (Statement s = conn.createStatement()) {
                try (ResultSet rs = s.executeQuery("SELECT * FROM TestData10k")) {
                    while (rs.next()) {
                        ps.setInt(1, rs.getInt(1));
                        ps.setString(2, rs.getString(2));
                        ps.setInt(3, rs.getInt(3));
                        ps.setDouble(4, rs.getDouble(4));
                        ps.setBoolean(5, rs.getBoolean(5));
                        ps.addBatch();
                    }
                    ps.executeBatch();
                    conn2.commit();
                }
            }
        }
    }
    sw.stop();
    System.out.println(sw.getTime());
} catch (Exception e) {
    e.printStackTrace(System.err);
}

Using that same table, my earlier approach using Jackcess' ImportUtil.importResultSet took about 3,100 ms to run (without having to create the destination table first)

connStr = "jdbc:ucanaccess://C:/Users/Public/UCanAccessDemo.accdb";
try (Connection conn = DriverManager.getConnection(connStr)) {
    StopWatch sw = new StopWatch();
    sw.start();
    String destinationPath = "C:/Users/Gord/Desktop/target.accdb";
    String tableName = "TestData10k";
    try (Statement s = conn.createStatement()) {
        try (ResultSet rs = s.executeQuery("SELECT * FROM [" + tableName + "]")) {
            // Jackcess calls:
            Database db = DatabaseBuilder.open(new File(destinationPath));
            ImportUtil.importResultSet(rs, db, tableName);  // create new table
        }
    }
    sw.stop();
    System.out.println(sw.getTime());
} catch (Exception e) {
    e.printStackTrace(System.err);
}

However, the following tweak to disable AutoSync reduced the Jackcess time to just over 300 ms

connStr = "jdbc:ucanaccess://C:/Users/Public/UCanAccessDemo.accdb";
try (Connection conn = DriverManager.getConnection(connStr)) {
    StopWatch sw = new StopWatch();
    sw.start();
    String destinationPath = "C:/Users/Gord/Desktop/target.accdb";
    String tableName = "TestData10k";
    try (Statement s = conn.createStatement()) {
        try (ResultSet rs = s.executeQuery("SELECT * FROM [" + tableName + "]")) {
            // Jackcess calls:
            Database db = new DatabaseBuilder()
                    .setAutoSync(false)
                    .setFile(new File(destinationPath))
                    .open();
            ImportUtil.importResultSet(rs, db, tableName);  // create new table
            db.flush();
        }
    }
    sw.stop();
    System.out.println(sw.getTime());
} catch (Exception e) {
    e.printStackTrace(System.err);
}

[Wayne Johnson]

Gord,
I tried your solution above. It does not copy into the existing (empty) table, it creates a new one with a "2" appended to the name.

Then I tried it by changing the importResultSet statement to use the existing table, like so:

        ImportUtil.importResultSet(rs, db, tableName,
                  new SimpleImportFilter(), true);  // copy existing table

That accomplished nothing, throwing a

com.healthmarketscience.jackcess.ConstraintViolationException ...
violates constraint Foreign Key  ...

Apparently it cannot handle references to other tables -- yes the referenced table is already in the database and contains the referenced key. I works for a table that has no references but fails for others that do.

[Gord Thompson]

Apparently it cannot handle references to other tables

Ah, yes. Access lets us insert arbitrary values into an AutoNumber column but Jackcess doesn't. Workarounds might include

1. Make the parent ID column Number(Long Integer) instead of AutoNumber in the destination table, or

2. Add code to track the mapping of old-to-new ID numbers in the parent table, then adjust the foreign key values in the child table(s) accordingly.

[Gord Thompson]

I have submitted a Jackcess feature request for an option to let us insert arbitrary values into AutoNumber fields. The initial response was positive.

If the feature gets implemented then it should be possible to insert existing values into an AutoNumber field without having to resort to the above workarounds, with Jackcess at least. (How UCanAccess might do it depends on whether HSQLDB can be convinced to co-operate.)

[Wayne Johnson]

Great! I hadn't tried prepared statements. Time for the copy is now down to 50 seconds.

But here's another issue. Copying this way regenerates keys (AutoID) in sequence from 1. In my source database table "A" there is some sparseness of the numbers because some items had been deleted. Now when I copy another table "B" that contains references to items in A. Those references are now in error. It gets

net.ucanaccess.jdbc.UcanaccessSQLException: integrity constraint violation: foreign key no parent

Know of a work-around for that?