Here we will learn how to attach the SQLite database with example and how to use attach database statements in SQLite to point to the particular database with example.
When we have multiple databases, we can use only one at a time. In SQLite, the ATTACH DATABASE statement is used to attach a particular database for the 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 a temporary database and a temporary table with the use of a temp database.
So, we can say that if you are connecting to a particular database, you can use two databases i.e. main and temp databases.
Following is the syntax which is used to attach a specific database for the current connection.
ATTACH DATABASE 'dbfilename' AS alias_name;
The above SQLite statement is used to attach the database file “dbfilename” to the current connection with the name alias_name.
First, we connect to the emp.db database using the 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 the above query we will get output like as shown below.
seq name file
--- --------------- -----------------
0 main C:\sqlite\emp.db
Here you can see that the emp.db database has given name main database. Now, we will try to attach some other database to the current connection using the ATTACH DATABASE command as follows.
Now, we again fire .databases meta-command to see all active databases that can we use.
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 a table called dept_master in myDB database and populate the data of dept_master table of the 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 the above queries we are creating table dept_master in myDB database and populating data from the emp.db database dept_master table. Now we will run the select statement like as shown below to see the records of myDB database dept_master table.
SELECT * FROM mydb.dept_master;
When we run the 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 a single connection with the ATTACH DATABASE command.