Copy tables between SQLite databases#

I figured out a pattern for doing this today using the sqlite3 CLI tool - given two SQLite databases in the current folder, called tils.db and simonwillisonblog.db:

1
echo "
2
attach database 'simonwillisonblog.db' as simonwillisonblog;
3
attach database 'tils.db' as tils;
4
drop table if exists simonwillisonblog.til;
5
create table simonwillisonblog.til as select * from tils.til;
6
update simonwillisonblog.til set shot = null;
7
" | sqlite3

I’m using that in this GitHub Actions workflow.

That last update simonwillisonblog.til set shot = null line is because the shot column contains a large BLOB screenshot image which I don’t need in the copied table.