18

I have SQLite databases named database1 with a table t1 and database2 with a table t2. I want to import table t2 from database2 into database1. What command(s) should I use?

gustafbstrom
  • 1,622
  • 4
  • 25
  • 44
sachit
  • 1,128
  • 2
  • 12
  • 25
  • 2
    Possible duplicate of [Copying data from one sqlite db to another](http://stackoverflow.com/questions/2359205/copying-data-from-one-sqlite-db-to-another) – Jukka Suomela Oct 30 '15 at 21:01

3 Answers3

41

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

Then open database1, attach the other database with the command:

ATTACH 'database2file' AS db2;

then create the table t2, and copy the data over with:

INSERT INTO t2 SELECT * FROM db2.t2;
CL.
  • 173,858
  • 17
  • 217
  • 259
16

Shell command:

sqlite3 database1

In SQLite shell:

sqlite> ATTACH 'database2' AS db2;
sqlite> CREATE TABLE t1 AS SELECT * FROM db2.t2;
Daniel Böhmer
  • 14,463
  • 5
  • 36
  • 46
Ivor O'Connor
  • 351
  • 3
  • 3
6

You could use the sqlite3 .dump command to pipe the dump output into the other db. It takes an optional argument with the table name.

db1=~/mydb1.sqlite 
db2=~/mydb2.sqlite
t=t2

sqlite3 "$db2" ".dump $t" | sqlite3 "$db1"

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

If the tables are big, this may be slow because it will do INSERTs. If they are huge, and it is really too slow, maybe .import would be faster. You could try something like

sqlite3 "$db2" ".schema $t" | sqlite3 "$db1"
sqlite3 "$db2" "SELECT * FROM $t" | sqlite3 "$db1" ".import /dev/stdin $t"
mivk
  • 13,452
  • 5
  • 76
  • 69