Working With Attached Databases in SQLite
SQLite allows the use of as many as ten attached databases in addition to the database loaded as "main". Tables from different databases can be included in the same query. Changes may be made to the schema and data contained in the attached databases. Attached databases may have table names that are identical to the database loaded as "main" or to other attached databases but a table cannot be created that duplicates a currently loaded table.
sqlite> .databases
| seq | name | file |
| --- | --------------- | ---------------------------------------------------------- |
| 0 | main | C:\Databases\sqlite\sales2007.sqlite |
| 1 | temp | C:\DOCUME~1\COLINR~1\LOCALS~1\Temp\etilqs_Fr4rkeXfxA2wWVBo |
ATTACH ' drive/path/database_name' AS database_alias;
sqlite> ATTACH 'sales2006.sqlite' AS lastyear;
sqlite> .databases
| seq | name | file |
| --- | --------------- | ---------------------------------------------------------- |
| 0 | main | C:\Databases\sqlite\sales2007.sqlite |
| 1 | temp | C:\DOCUME~1\COLINR~1\LOCALS~1\Temp\etilqs_Fr4rkeXfxA2wWVBo |
| 2 | lastyear | C:\Databases\sqlite\sales2006.sqlite |
An unfortunate problem is that dot commands such as ".tables ", ".dump ", ".schema " will only list objects of the database loaded as main not those of the attached databases. Consequently you have to know the names of the attached database tables in order to refer to them.
While there is no command to list the names of the tables in an attached database, if you do know which tables are there, you can get the table structure with the SQLite specific PRAMGMA table_info statement.
PRAGMA table_info(table_name); /* Will work if the table name is not duplicated in another database */
PRAGMA database_alias.table_info(table_name);
sqlite> PRAGMA lastyear.table_info(gross_sales);
cid|name|type|notnull|dflt_value|pk
0|year|INTEGER|0|'2006'|0
1|month|TEXT|0||0
2|monthlygross|REAL|0||0
3|sortcol|INTEGER|0||0
sqlite>
In the following example we wish to compare this year gross sales by month for a business against those of last year. As it happens the monthly sales figures for 2007 are in a table called "gross_sales" in the database loaded as "main" and the figures for 2006 are in a table also called "gross_sales" which can be found in the attached database "lastyear". In addition both "gross_sales" tables use duplicate column names. This requires the use of dot notation in the query in order to specify the database, table and field being referred to as shown below.
main. table_name . field_name
database_alias. table_name . field_name
sqlite> .mode columns
sqlite> ..width 10 15 15
sqlite> sqlite> SELECT main.gross_sales.month,main.gross_sales.monthlygross , lastyear.gross_sales.monthlygross FROM main.gross_sales,lastyear.gross_sales WHERE main.gross_sales.sortcol = lastyear.gross_sales.sortcol ;
| month | monthlygross | monthlygross |
| ---------- | --------------- | --------------- |
| January | 27580.56 | 34000.12 |
| February | 29321.58 | 32453 |
| March | 47412.42 | 45002.02 |
| April | 49300.43 | 47210.33 |
| May | 48990.52 | 56778.43 |
| June | 51014.23 | 48944.5 |
| July | 42530.89 | 45300 |
| August | 37899.25 | 53020.12 |
| September | 38596.56 | 51012.23 |
| October | 33015.55 | 34500 |
| November | 23564.56 | 27802.09 |
| December | 17825.13 | 19330.94 |
The "main.gross_sales.sortcol" and "lastyear.gross_sales.sortcol" columns which are used in the where clause are a numeric representation of the month so that only sales from the same month are compared with each other. Since the field names for "gross_sales" tables for 2007 and 2006 are identical there are two columns called "monthlygross" in the result. In this next example we will give columns for each year an alias and we will also calculate the percentage change in sales.
SELECT main.gross_sales.month 'Month',main.gross_sales.monthlygross '2007' , lastyear.gross_sales.monthlygross '2006' , ROUND((((main.gross_sales.monthlygross -lastyear.gross_sales.monthlygross) / lastyear.gross_sales.monthlygross )*100),2 ) 'Percent Change' FROM main.gross_sales,lastyear.gross_sales WHERE main.gross_sales.sortcol = lastyear.gross_sales.sortcol;
| Month | 2007 | 2006 | Percent Change |
| ---------- | ---------- | ---------- | -------------- |
| January | 27580.56 | 34000.12 | -18.88 |
| February | 29321.58 | 32453 | -9.65 |
| March | 47412.42 | 45002.02 | 5.36 |
| April | 49300.43 | 47210.33 | 4.43 |
| May | 48990.52 | 56778.43 | -13.72 |
| June | 51014.23 | 48944.5 | 4.23 |
| July | 42530.89 | 45300 | -6.11 |
| August | 37899.25 | 53020.12 | -28.52 |
| September | 38596.56 | 51012.23 | -24.34 |
| October | 33015.55 | 34500 | -4.3 |
| November | 23564.56 | 27802.09 | -15.24 |
| December | 17825.13 | 19330.94 | -7.79 |
Detaching a Database
To detach a database use the following command.
DETACH database_alias;
Opening SQLite from Windows Explorer
When SQLite is used in the standard manner by using the command "sqlite3 database_name " at the command prompt then a file is created if it does not already exist to store database changes as they occur.
If on the other hand, you open SQLite in MS Windows by clicking on sqlite3.exe in Windows Explorer then the command prompt window will be opened without being attached to a database. What this means is that any database objects created or data entered will exist only in the RAM memory and will disappear once the command prompt window is closed unless they are copied to an attached database. This page explains how to do that.
That the database exists only in the temporary memory can be shown in this particular instance by entering ".databases" at the command prompt and pressing enter. The result should look like the following.
SQLite version 3.5.2
Enter ".help" for instructions
| sqlite> .databases | ||
| seq | name | file |
| --- | --------------- | ---------------------------------------------------------- |
| 0 | main | |
| sqlite > | ||
Note that the listing to the right of "main" and under the file column is blank, which means that there is no file designated to save the database objects to. In addition if you now enter ".tables" at the prompt and press enter. The result will be an empty prompt as no tables yet exist in the workspace.
sqlite>
sqlite> .tables
sqlite>
Let's create a table using the following statement.
CREATE TABLE Customers(Acctnumber INTEGER PRIMARY KEY,Custname VARCHAR(50),Addr1 VARCHAR(50),Addr2 VARCHAR(50),City VARCHAR(30),State CHAR(2),Zipcode VARCHAR(10),Contact VARCHAR(30),Phone VARCHAR(10));
sqlite> CREATE TABLE Customers(Acctnumber INTEGER PRIMARY KEY,Custname VARCHAR(5
0),Addr1 VARCHAR(50),Addr2 VARCHAR(50),City VARCHAR(30),State CHAR(2),Zipcode VA
RCHAR(10),Contact VARCHAR(30),Phone VARCHAR(10));
sqlite> .tables
Customers
| sqlite> .databases | ||
| seq | name | file |
| --- | --------------- | ---------------------------------------------------------- |
| 0 | main | |
| 1 | temp | C:\DOCUME~1\COLINR~1\LOCALS~1\Temp\etilqs_c6rO5UpzkcPd9jEr |
| sqlite > | ||
The temp database is automatically created when something is created in the main database. It will hold your work during the session but which will disappear when you quit the program. If the command ".tables" is again entered at the prompt, we can see that the table " Customers"now exists. However when the simple select query "select * from Customers;" is entered the result is a blank prompt because the table is empty.
sqlite> .tables
Customers
sqlite>
sqlite> select * from Customers;
sqlite>
Now if we paste the following statements at the prompt, the data will be loaded into the table.
INSERT INTO Customers(Acctnumber, Custname,Addr1,Addr2,City,State,Zipcode ,Contact,Phone) VALUES(
130169,'Acme Widgets','1744 Alder Road','Apt 31C','Springfield','VA','20171','Alan Allen','5715551267');
INSERT INTO Customers(Acctnumber, Custname,Addr1,Addr2,City,State,Zipcode ,Contact,Phone) VALUES(
130208,'Nike Missiles Inc','5946 Oak Drive','','Springfield','VA','20171','Lucy Baker','5715558762');
INSERT INTO Customers(Acctnumber, Custname,Addr1,Addr2,City,State,Zipcode ,Contact,Phone) VALUES(
130247,'Charlies Bakery','7116 Ginko St','suite 100','Springfield','VA','20171','Susan Nordstrom','5715552363');
INSERT INTO Customers(Acctnumber, Custname,Addr1,Addr2,City,State,Zipcode ,Contact,Phone) VALUES(
130286,'Unisales Inc.','8438 Maple Ave',' ','Springfield','VA','20171-3521','Roger Norton','5715551418');
INSERT INTO Customers(Acctnumber, Custname,Addr1,Addr2,City,State,Zipcode ,Contact,Phone) VALUES(
130325,'M.I. Sinform & Sons','1785 Elm Avenue','P.O. Box 31','Springfield','VA','20171','Mike I. Sinform','5715558760');
INSERT INTO Customers(Acctnumber, Custname,Addr1,Addr2,City,State,Zipcode ,Contact,Phone) VALUES(
130364,'Big Dents Towing Inc.','7578 Spruce St.','Building 31 A','Springfield','VA','20171-5231','George Spencer','5715557855');
When the "select * from Customers;" is reentered the we can see that the data has been entered correctly. Note the " .headers ON " command that preceeded the select query which tells SQLite that I want the field names to be listed as well as the data. This has only to be entered once during the session.
sqlite> .headers ON
sqlite> select * from Customers;
Acctnumber|Custname|Addr1|Addr2|City|State|Zipcode|Contact|Phone
130169|Acme Widgets|1744 Alder Road|Apt 31C|Springfield|VA|20171|Alan Allen|5715551267
130208|Nike Missiles Inc|5946 Oak Drive||Springfield|VA|20171|Lucy Baker|5715558762
130247|Charlies Bakery|7116 Ginko St|suite 100|Springfield|VA|20171|Susan Nordstrom|5715552363
130286|Unisales Inc.|8438 Maple Ave||Springfield|VA|20171-3521|Roger Norton|5715551418
130325|M.I. Sinform & Sons|1785 Elm Avenue|P.O. Box 31|Springfield|VA|20171|Mike I. Sinform|5715558760
130364|Big Dents Towing Inc.|7578 Spruce St.|Building 31 A|Springfield|VA|20171-5231|George Spencer|5715557855
sqlite>
Using the ATTACH statement we can create a persistant database file or open it if it already exists. Note that the file name is in quotes. It is not necessary for the file name to have a suffix but it is not a bad idea in order distinguish sqlite databases from other types of files. If we once again query the database list with the ".databases" command we can see that the file has the alias "newdb". Currently the table we created exists only in the memory.
sqlite>ATTACH 'ServiceCtr.db' AS newdb;
| sqlite> .databases | ||
| seq | name | file |
| --- | --------------- | ---------------------------------------------------------- |
| 0 | main | |
| 1 | temp | C:\DOCUME~1\COLINR~1\LOCALS~1\Temp\etilqs_c6rO5UpzkcPd9jEr |
| 2 | newdb | C:\Databases\sqlite\ServiceCtr.db |
| sqlite > | ||
The easiest way of saving the table to the attached database is to use the statement "CREATE TABLE attached_database.tablename AS SELECT * FROM main.tablename" which selects all the columns and all the records from the table in "main" as shown below.
CREATE TABLE attached_database.tablename AS SELECT * FROM main.tablename;
CREATE TABLE attached_database.tablename AS SELECT field1,field2,field3 FROM main.tablename;
CREATE TABLE newdb.Customers AS SELECT Acctnumber, Custname ,Addr1 ,Addr2 ,City ,State ,Zipcode ,Contact ,Phone FROM main.Customers ;
sqlite>
sqlite> select * from newdb.Customers;
Acctnumber|Custname|Addr1|Addr2|City|State|Zipcode|Contact|Phone
130169|Acme Widgets|1744 Alder Road|Apt 31C|Springfield|VA|20171|Alan Allen|5715551267
130208|Nike Missiles Inc|5946 Oak Drive||Springfield|VA|20171|Lucy Baker|5715558762
130247|Charlies Bakery|7116 Ginko St|suite 100|Springfield|VA|20171|Susan Nordstrom|5715552363
130286|Unisales Inc.|8438 Maple Ave||Springfield|VA|20171-3521|Roger Norton|5715551418
130325|M.I. Sinform & Sons|1785 Elm Avenue|P.O. Box 31|Springfield|VA|20171|Mike I. Sinform|5715558760
130364|Big Dents Towing Inc.|7578 Spruce St.|Building 31 A|Springfield|VA|20171-5231|George Spencer|5715557855
sqlite>
Now the data will be stored in the ServiceCenter.db file. Once again a simple query will verify that the records have been transferred.