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:

echo "
attach database 'simonwillisonblog.db' as simonwillisonblog;
attach database 'tils.db' as tils;
drop table if exists simonwillisonblog.til;
create table simonwillisonblog.til as select * from tils.til;
update simonwillisonblog.til set shot = null;
" | 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.

Created 2023-04-03T10:32:17-07:00, updated 2023-04-03T11:24:48-07:00 · History · Edit