0

I have SQLite databases db1 and db2. I need identical tables in each: publications. How can I write a .sql file where I don't need to duplicate create table? Something like this:

CREATE TABLE
  IF NOT EXISTS db1.publications,
  IF NOT EXISTS db2.publications(
    id TEXT,
    content TEXT
    ... a bit long...
);
user4157124
  • 2,809
  • 13
  • 27
  • 42
charnould
  • 2,480
  • 3
  • 19
  • 23
  • If you do it from command line, [here is an instruction](https://stackoverflow.com/questions/38832802/sqlite3-dump-schema-into-sql-file-from-command-line). To do it from software, you should specify approx which language you use. [Example from Python](https://stackoverflow.com/questions/11996394/is-there-a-way-to-get-a-schema-of-a-database-from-within-python). You should then just add the "if not exists..." part and run for each database. – MyICQ Jun 20 '23 at 09:40

1 Answers1

0

Found a working way:

CREATE TABLE users.drafts AS SELECT * FROM main.events WHERE 0;
charnould
  • 2,480
  • 3
  • 19
  • 23
  • 2
    This does not create an exact duplicate of the original table because it does not copy the definitions of the primary key, foreign keys, constraints and indexes. – forpas Jun 20 '23 at 11:14
  • @forpas You're right, but for my specific problem it does the job. – charnould Jun 20 '23 at 16:19
  • @forpas Do you know a way to clone database *including* primary key? Thanks. – charnould Jul 22 '23 at 16:35
  • 1
    The simplest way to do this is to export the db as .sql file and execute it. Check this thread: https://stackoverflow.com/questions/38832802/sqlite3-dump-schema-into-sql-file-from-command-line and its accepted answer and this: https://stackoverflow.com/questions/11643611/execute-sqlite-script – forpas Jul 22 '23 at 17:45