How to import tables from another database into sqlite?

Question

I have a SQLite database1 named database1 with table t1 and database2 with table t2 . I want to import table t2 from database2 to database1 . Which commands should be used?

Answer 1

Open database2 with the sqlite3 command line tool and read the table definition with the .schema t2 command. (Alternatively, use any other tool that allows you to read the table definition.)

Then open database1 , attach another database with the command:

 ATTACH 'database2file' AS db2; 

then create table t2 and copy the data with:

 INSERT INTO t2 SELECT * FROM db2.t2; 

Answer 2

Shell Command:

 sqlite3 database1 

In the SQLite shell:

 sqlite> ATTACH 'database2' AS db2; sqlite> CREATE TABLE t1 AS SELECT * FROM db2.t2; 

Answer 3

You can use the sqlite3 .dump .dump to output dump output to another db. It takes an optional argument with the name of the table.

 db1=~/mydb1.sqlite db2=~/mydb2.sqlite t=t2 sqlite3 "$db2" ".dump $t" | sqlite3 "$db1" 

If you do not have common tables in both databases, you can leave the table name and copy all the tables.

If the tables are large, this can be slow because INSERT will do this. If they are huge, and it is really too slow, perhaps .import will be faster. You can try something like

 sqlite3 "$db2" ".schema $t" | sqlite3 "$db1" sqlite3 "$db2" "SELECT * FROM $t" | sqlite3 "$db1" ".import /dev/stdin $t"