Merge multiple SQLite databases using bash?

Question

I have multiple sqlite databases with the same schema, and i want to merge them all together, since i have a unique column and there might be risks of duplicates, i am using insert or ignore, in sqlite it would be easy :

sqlite3 database.db3
sqlite> attach './db1.db3' as s1;
sqlite> attach './db2.db3' as s2;
sqlite> attach './db3.db3' as s3;
sqlite> insert or ignore into table (c1, c2, c3) select c1, c2, c3 from s1.table;
sqlite> insert or ignore into table (c1, c2, c3) select c1, c2, c3 from s2.table;
sqlite> insert or ignore into table (c1, c2, c3) select c1, c2, c3 from s3.table;
sqlite> .exit

I read about dump but i dont want to merge the whole schema, just one table, so i thought about this solution, now till here everything is fine but i need to run all this via a bash, i tried the following but it doesnt :

sqlite3 database.db3 "attach './db1.db3' as s1;"
sqlite3 database.db3 "attach './db2.db3' as s2;"
sqlite3 database.db3 "attach './db3.db3' as s3;"
sqlite3 database.db3 "select count(*) from s1.table;"
sqlite3 database.db3 "select count(*) from s2.table;"
sqlite3 database.db3 "select count(*) from s3.table;"

it says Error: no such table: s1.table

What am i doing wrong

dogbane · Accepted Answer · 2014-01-28 10:38:07Z

6

Use a heredoc as shown below:

sqlite3 database.db3 << "EOF"
attach './db1.db3' as s1;
attach './db2.db3' as s2;
attach './db3.db3' as s3;
insert or ignore into table (c1, c2, c3) select c1, c2, c3 from s1.table;
insert or ignore into table (c1, c2, c3) select c1, c2, c3 from s2.table;
insert or ignore into table (c1, c2, c3) select c1, c2, c3 from s3.table;
.exit
EOF

Alternatively, put all your commands into a file and run it like this:

sqlite3 database.db3 < commandsFile

answered Jan 28, 2014 at 10:38

dogbane

276k77 gold badges407 silver badges415 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BMW · Accepted Answer · 2014-01-28 10:42:26Z

1) Create a text file with the lines you want to enter into the sqlite command line program, like this:

attach './db1.db3' as s1;
attach './db2.db3' as s2;
attach './db3.db3' as s3;
insert or ignore into table (c1, c2, c3) select c1, c2, c3 from s1.table;
insert or ignore into table (c1, c2, c3) select c1, c2, c3 from s2.table;
insert or ignore into table (c1, c2, c3) select c1, c2, c3 from s3.table;
.exit

and then just call sqlite3 database.db < commands.txt

2) using shell

#!/bin/bash 

sqlite3 database.db <<"EOF"
attach './db1.db3' as s1;
attach './db2.db3' as s2;
attach './db3.db3' as s3;
insert or ignore into table (c1, c2, c3) select c1, c2, c3 from s1.table;
insert or ignore into table (c1, c2, c3) select c1, c2, c3 from s2.table;
insert or ignore into table (c1, c2, c3) select c1, c2, c3 from s3.table;
.exit
EOF

Collectives™ on Stack Overflow

Merge multiple SQLite databases using bash?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related