There's got be an easy way to copy a table from sqlLite DB1 to DB2

General
1

I just want to copy a table from one database file to another. The table doesn’t have a primary key nor default values, it’s just fields and data.

This has got to be easy but I can’t find it.

Ideas?

1 Like
2

SQL SELECT INTO Statement (w3schools.com)

3

just missing the to another database file part.

Thanks,

4

Use the ATTACH statement. https://www.sqlite.org/lang_attach.html

Then you can use SELECT INTO to copy the table between the two.

5

You can attach a 2nd database file using http://documentation.xojo.com/api/databases/sqlitedatabase.html#sqlitedatabase-adddatabase, then copy data to or from the 2nd database file.

6

Well, I learned something new. I didn’t notice the AddDatabase feature in 2019r2. Time to revamp some of my code…

1 Like
7

So I tried this: wp.cc.teamsDB.addDatabase(userArchiveDB.databaseFile, “archive”) and got an error that says "not enough arguments: missing FolderItem value for parameter “file”

Any ideas here?

I also tried it with attach and detach database. The tables were not saved in the new database file.

8

Something like:


Var dbh as sqlitedatabase

// Here, connect dbh to your source database

dbh.ExecuteSQL ("attach database '/path/to/second/db' as db2")
dbh.ExecuteSQL ("create table db2.tablename as select * from main.tablename")
dbh.ExecuteSQL ("insert into db2.tablename select * from main.tablename")

.
is what you want. I do this all the time, although in my case I’m moving a single row from one database to another.

tablename is whatever the name of your table to be copied is.

9

So, you want to copy a TABLE you do not created ?

And this is a SQLite file ?

10

The password parameter must be specified. Try:

wp.cc.teamsDB.addDatabase(userArchiveDB.databaseFile, "archive", "")
12

Yes sqlite. It just seems like copying the whole table from one database to another should be easy.

13

Thanks. fyi, the language reference has an example without the password:

Var addDBFile As New FolderItem(“AddDB.sqlite”)

Try
currentDB.AddDatabase(AddDBFile, “locations”)
MessageBox(“Database attached.”)

Catch error As IOException
MessageBox(“The database could not be added.”)

End Try

14

It is, see my post.

15

As easy as it gets . . .