Latest Post

Error: No input file specified.

While trying to run a codeigniter app the other day I came across this error just after I had finished uploading the files and making the ne...

Tuesday, June 20, 2017

SQLITE How to work with multiple databases at the same time

Let us suppose you have 3 (or however many) databases that you want to work with in sqlite e.g. say you have a Dev, Test and a Production database which you want to transfer data between them.
  1. Open a sqlite command prompt
  2. Open to to the databases you want to work with using the .open command as well as the attach command
    1. sqlite> .open /path/to/Dev.db
    2. sqlite> attach '/path/to/Test.db' as test
    3. sqlite> attach '/path/to/Productiom.db' as prod 
  3. Verify the opened databases using .databases command to confirm that you have all 3 databases opened 

  4. Now you can run sql statements against tables in any of the databases e.g.
    1. For tables in Dev.db you can do a direct select * from tbl2 or insert into tbl2 etc
    2. For table in the Test.db you will need to prefix the table name with the alias specifiedwhen you attached the Test.db i.e. test and likewise for Production.db tables you will need to prefix the tables with prod
      e.g. insert into test.tbl2 select * from prod.tbl2 to copy
      data from Production.db table into a Test.db table

Chitika Ads