What is Attach Database in SQLite? How to use it?

SQLite Attach Database

When we have multiple databases, we can use only one at a time. In SQLite ATTACH DATABASE statement is used to attach particular database for current connection.

In SQLite whenever we are connecting to a specific database, its name is main regardless of what’s its filename. We can also work with temporary database and temporary table with use of temp database.

So, we can say that if you are connecting to a particular database, you can use two database i.e. main and temp databases.

Syntax of SQLite Attach Database

Following is the syntax which is used to attach specific database for current connection.

ATTACH DATABASE 'dbfilename' AS alias_name;

 Above sqlite statement is used to attach database file “dbfilename” to current connection with the name alias_name.

Example of SQLite Attach Database

First, we connect to emp.db database using sqlite3 command as follows.

C:\sqlite>sqlite3 emp.db

We will fire .databases meta command to see the current connection.

sqlite> .databases

When we run above query we will get output like as shown below.

seq  name             file

---  ---------------  -----------------

0    main             C:\sqlite\emp.db

Here you can see that emp.db database has given name main database. Now, we will try to attach some other database to current connection using ATTACH DATABASE command as follows.

ATTACH DATABASE 'c:\sqlite\db1.db' as myDB;

Now, we again fire .databases meta command to see all active database that can we use.

sqlite> .databases

When we run above query we will get output like as shown below

seq  name             file

---  ---------------  ----------------------------

0    main             C:\sqlite\emp.db

2    myDB             c:\sqlite\db1.db

Here, it will return 2 databases main and myDB. Now, we will create table called dept_master in myDB database and populate the data of dept_master table of emp.db database.

CREATE TABLE myDB.dept_master(

dept_id INTEGER PRIMARY KEY AUTOINCREMENT,

dept_name TEXT NOT NULL);

INSERT INTO myDB.dept_master

select dept_id, dept_name FROM dept_master;

If you observe above queries we are creating table dept_master in myDB database and populating data from emp.db database dept_master table. Now we will run select statement like as shown below to see the records of myDB database dept_master table.

SELECT * FROM mydb.dept_master;

When we run above sqlite statement we will get records like as shown below

dept_id     dept_name

----------  ----------

1           Admin

2           Sales

3           Quality Co

4           Marketing

So this is how we can work with multiple databases using single connection with ATTACH DATABASE command.