{"id":771,"date":"2016-01-03T12:18:19","date_gmt":"2016-01-03T05:18:19","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=771"},"modified":"2022-04-03T15:04:00","modified_gmt":"2022-04-03T08:04:00","slug":"sqlite-attach-database","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-attach-database\/","title":{"rendered":"SQLite Attach Database"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn how to attach additional databases to the current database connection using the SQLite ATTACH DATABASE<\/code> statement.<\/p>\r\n\r\n\r\n\r\n

Introduction to the SQLite ATTACH DATABASE statement<\/h2>\r\n\r\n\r\n\r\n

When you connect to a database, its name is main<\/code> regardless of the database file name. In addition, you can access the temporary database that holds temporary tables and other database objects via the temp<\/code> database.<\/p>\r\n\r\n\r\n\r\n

Therefore, every SQLite database connection has the main<\/code> database and also temp<\/code> database in case you deal with temporary database objects.<\/p>\r\n\r\n\r\n\r\n

To attach an additional database to the current database connection, you use the ATTACH DATABASE<\/code> statement as follows:<\/p>\r\n\r\n\r\n

ATTACH DATABASE file_name AS database_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The statement associates the database file file_name<\/code> with the current database connection under the logical database name database_name<\/code>.<\/p>\r\n\r\n\r\n\r\n
If the database file file_name<\/code> does not exist, the statement creates a new database file.<\/p>\r\n\r\n\r\n\r\n
Once the additional database attached, you can refer to all objects in the database under the name database_name<\/code>. For example, to refer to the people<\/code> table in the contacts<\/code> database, you use the contacts.people<\/code>.<\/p>\r\n\r\n\r\n\r\n
In case you want to create a new memory database and attach it to the current database connection, you use :memory:<\/code> filename.<\/p>\r\n\r\n\r\n\r\n
You can attach multiple in-memory databases at the same time with a condition that each memory database must be unique.<\/p>\r\n\r\n\r\n\r\n
If you specify an empty file name ''<\/code>, the statement creates a temporary file-backed database.<\/p>\r\n\r\n\r\n\r\n
Note that SQLite automatically deletes all temporary and memory databases when the database connection is closed.<\/p>\r\n\r\n\r\n\r\n
SQLite ATTACH DATABASE example<\/h2>\r\n\r\n\r\n\r\n
First, connect to the chinook sample database<\/a> using sqlite3<\/code> command as follows:<\/p>\r\n\r\n\r\n
>sqlite3<\/span> c<\/span>:\\sqlite<\/span>\\db<\/span>\\chinook<\/span>.db<\/span>;<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Next, use the .databases<\/code> command to list all databases in the current database connection.<\/p>\r\n\r\n\r\n
sqlite> .databases<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
SQLite returns the following output.<\/p>\r\n\r\n\r\n
seq<\/span>  name<\/span>             file<\/span>\r\n---<\/span>  ---------------<\/span>  ----------------------------------------------------------<\/span>\r\n0    main<\/span>             c<\/span>:\\sqlite<\/span>\\db<\/span>\\chinook<\/span>.db<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Then, use the ATTACH DATABASE<\/code> statement to create a new database named contacts<\/code> and associates it in the current database connection.<\/p>\r\n\r\n\r\n
sqlite> attach database 'c:\\sqlite\\db\\contacts.db'<\/span> as<\/span> contacts;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Fourth, use the .database<\/code> command to display all databases in the current database connection.<\/p>\r\n\r\n\r\n
sqlite> .databases<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
SQLite returns 2 databases as follows:<\/p>\r\n\r\n\r\n
seq  name             file\r\n---  ---------------  ----------------------------------------------------------<\/span>\r\n0    main             c:\\sqlite\\db\\chinook.db\r\n2    contacts         c:\\sqlite\\db\\contacts.db<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
sqlite<\/span>> CREATE<\/span> TABLE<\/span> contacts<\/span>.people<\/span>(first_name<\/span> text<\/span>, last_name<\/span> text<\/span>);\r\nsqlite<\/span>> INSERT<\/span> INTO<\/span> contacts<\/span>.people<\/span> SELECT<\/span> firstName<\/span>, lastName<\/span> FROM<\/span> customers<\/span>;<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
Notice that we referred to the people<\/code> table in the contacts<\/code> database using the contacts.people<\/code> naming convention.<\/p>\r\n\r\n\r\n\r\n
SELECT<\/span> * FROM<\/span> contacts.people;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
In this tutorial, you have learned how to use the SQLite ATTACH DATABASE<\/code> statement to associate additional databases in the current database connection.<\/p>\r\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
For more information on the ATTACH DATABASE<\/code> statement, check out its documentation<\/a>.<\/p>\r\n\r\n\r\n\r\n
Finally, query data<\/a> from the people<\/code> table in the contacts<\/code> database.<\/p>\r\n\r\n\r\n
After that, create a new table<\/a> named people<\/code> in the contacts<\/code> database and populate data from the customers<\/code> table in the main<\/code> database.<\/p>\r\n\r\n\r\n