SQLite - ATTACH DATABASE

Consider a situation when there are multiple databases available and it is required to use any one of them at a time. The SQLite ATTACH DATABASE statement can be used to add a particular database file to the current database connection.

After this command, all SQLite statements will be executed under the attached database. If the database file does not exist, it will be created when the command is executed.

Syntax

The syntax of using SQLite ATTACH DATABASE statement is given below:

ATTACH [DATABASE] 'filename' AS database_name;

Parameters

`DATABASE`	`Optional.` The command can be run as either ATTACH DATABASE or ATTACH.
`filename`	`Required.` Specify the name of the database file to attach to the current database connection.
`database_name`	`Required.` Specify the logical name for the database file to use within the context of the current database connection.

Example: Attach a database file

Lets first use the .database dot command to see the list of databases available in the current connection.

sqlite> .database
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db

To attach a database file called example.db, the following command can be used:

sqlite> ATTACH DATABASE '/home/sqlite/example.db' AS 'example';

After attaching the database file, the .database dot command can be used again to see the list of databases available in the current connection.

sqlite> .database
seq  name             file
---  ---------------  ----------------------
0    main             /home/sqlite/testDB.db
2    example          /home/sqlite/example.db

The database names main and temp are reserved for the primary database and database to hold temporary tables and other temporary data objects. Both of these database names exist for every database connection and should not be used for attachment. If these reserved database names are used, the following warning message will be displayed:

sqlite> ATTACH DATABASE '/home/sqlite/example.db' AS 'TEMP';
Error: database TEMP is already in use
sqlite> ATTACH DATABASE '/home/sqlite/example.db' AS 'MAIN';
Error: database MAIN is already in use