Merge all SQLite databases with different tables

Question

Is there a cheap/fast way to merge SQLite db files with different tables?

For example:

• a.db has only table a
• b.db has only table b
• ...

I want to merge these into a abcd.db that contains all tables a, b, c, and d.

I think what I want is such a magic script:

merge_script a.db b.db c.db d.db -o abcd.db

I have noticed this tip and another tip and the ATTACH trick, which insert all records into "main" database, but can I do this while tables are absent in the "main" database?

UPDATE

I use SQLIte database files as simple storage container.

In most cases, I store each type of data (differed by table name) in a single file, and then merge them into "target" database.

But there are some types of data that should be in the same table name. if use sqlite3 .dump there would be a conflict in tablename.

Yet the .dump approach is very simple, I'll just do some workaround and use it.

Answer 1

Just dump and restore:

for db in a, b, c, d; { sqlite3 ${db}.db .dump | sqlite3 abcd.db }

[update]

another question, is there any quick fix if tables are not all unique, and I want to merge contents? tdihp

This is a little too broad a question. For example, what would you do with primary key violations? I would do this with some script language like Python in order to cope with error conditions (sqlalchemy.SqlSoup is excelent for this kind of thing).

That said, if you know the table already exists and there is no unique constraints, you can probably get way with some adhoc bash stunt - assuming that destinationtablename and sourcetablename have the same structure:

$ ( echo '.mode insert destinationtablename'
    echo 'select * from sourcetablename;' 
  ) |  sqlite3 -batch -init - source.db \
    |  sqlite3 -batch destination.db

You may not be aware that long comment threads are considered rude in this site, instead you are encouraged to update your question. A better question will:

• help other users with similar questions in the future
• earn you reputation points that gives you privileges (points also can be used for bounties later)