SQL INSERT INTO Statement
FREE
Overview
The SQL INSERT INTO statement is a command that is used to insert data into tables. Using the insert into table SQL, we can insert new single or multiple records in a table of relational databases. We can also copy the data from one table to another table using the INSERT INTO SELECT statement. For using the INSERT INTO SELECT statement, the data types of the source and the destination tables have to be the same.
Introduction
The SQL INSERT INTO statement is a SQL query or command used to insert data into tables. Using the insert into table SQL, we can insert new single or multiple records in a table of relational databases (databases like ORACLE, MySQL, MS-Access, IBM DB2, etc.).
Note:
- A row of a database table is also known as a record or a tuple.
- A column of a database table is also known as an attribute.
For inserting the data into the table, we have two ways:
- The first one is by using the insert into table SQL command.
- We can also insert new records in the table using the SQL insert into the select statement. We will learn about both ways later in this article.
SQL INSERT INTO Statement
Before learning about inserting into table SQL, let us first get a brief introduction to SQL and database management. So, SQL or Structured Query Language is used to manipulate and communicate with the data stored as tables in the databases. To manipulate the data, we first need to insert the data into the tables.
INSERT is a DML (Data Manipulation Language) command used in SQL and SQL-related relational databases. To insert the data into table SQL, we need SQL INSERT INTO statement. Let us learn about the insert into table SQL in detail.
Inserting Data without Using SELECT Clause
As we have seen, we have two ways of inserting the record(s), the first option, i.e., insert into table SQL can be done in two different forms namely - by specifying the column names with the INSERT INTO statement and second by not specifying the column names with the INSERT INTO statement. Let us learn the syntax of both ways.
1. INSERT INTO by Specifying the Column Names
We can insert new record(s) into the table of the database by mentioning the specific column names. By specifying the column names, we get flexibility in the insertion of values, as we can change the order of columns and values from the actual order of columns present in the table of the database.
Syntax:
2. INSERT INTO without Specifying the Column Names
We can insert new record(s) or values into the table of the database directly (without mentioning the column names). The syntax getter smaller when we do not specify the column names (the previous way). But, one of the drawbacks associated with the current way is that we do not get the flexibility, we cannot change the order of values from the actual order of columns present in the table of the database.
Syntax:
Let us discuss a few examples to understand the topic better.
Examples
We will now take scenarios of various databases and insert new record(s) using the insert into table SQL. Let us discuss different ways of insertions with examples.
Insert Values to All Columns
Suppose we have the database of the citizens of a city. We have three columns namely - the citizen name, age, and Aadhar number of the citizens. We want to insert new records in the citizen table. As we have seen, for inserting values in all the columns, we have two ways:
By Specifying the Column Names
We can insert new record(s) into all the columns of the citizen table of the database by mentioning the specific column names.
Example:
Without Specifying the Column Names
We can also insert new record(s) into all the columns of the citizen table of the database without mentioning the specific column names.
Example:
Output:
| Aadhar_No | Name | Age |
|---|---|---|
| 123456 | Tom | 21 |
Note The order of the column in the insert statement has to be the same as that of the table present in the database.
Insert Data Only in Specified Columns
We can insert new record(s) into the specified columns of the citizen table of the database by mentioning the names of the specific columns.
Suppose we have the database of the students of a school. We have four columns in the students table - Name, Age, Section, and Roll Number. But, we only want to store the Name, Roll Number, and Age of a student in the student table. So, we have to use the INSERT INTO clause with the specific column names.
Output:
| Roll_number | Name | Age |
|---|---|---|
| 12 | Tom | 15 |
Note: As we have specified the column names, the order of the column in the insert statement need not be the same as that of the table present in the database.
Insert Multiple Records
Till now we have seen single record insertion but we can also insert multiple records in the table. Let us take an example to visualize the insertion in a better way.
Suppose, we have a database of the counties of the world. The country table has three columns- country name, country code, and the continent (in which the country lies). So, let us insert records of various countries in the country table.
1. By specifying the names of the columns of the table:
2. Without specifying the names of the columns of the table:
Output:
| Name | Code | Continent |
|---|---|---|
| India | IND | Asia |
| France | FRN | Europe |
| Japan | JPN | Asia |
| Brazil | BRZ | Africa |
SQL INSERT INTO SELECT Statement
We can copy the data from one table to another table using the INSERT INTO SELECT statement.
Note:
- For using the INSERT INTO SELECT statement, the data types of the source and the destination tables have to be the same.
- The original data of the source table and the existing records of the destination tables are not affected.
As the other INSERT INTO statement, we have two ways of copying the data into the other table.
1. Without specifying the names of the columns of the table:
We can copy all the columns of one table to the other table simply without mentioning the names of the columns.
SYNTAX:
2. By specifying the names of the columns of the table:
We can copy the specific columns of one table to the other table by mentioning the names of the required columns.
Syntax:
Note: WHERE clause is optional. We can use the WHERE clause if we want only some specific conditional records to be copied from the source table to the destination table or output.
Let us take an example to visualize the INSERT INTO SELECT clause.
Suppose we have a database of the employees of a company. The employee table has four columns namely Employee ID, Name, Country, and Salary. We want to copy the data of the employee table to another table namely companyData.
1. Without specifying the names of the columns of the table:
2. By specifying the names of the columns of the table:
Conclusion
- INSERT is a DML (Data Manipulation Language) command used in the SQL and SQL-related relational databases. To insert the data into table SQL, we need SQL INSERT INTO statement.
- Using the insert into table SQL, we can insert new single or multiple records in a table of relational databases.
- We can insert records by specifying the column names with the INSERT INTO statement and without specifying the column names with the INSERT INTO statement.
- We can copy the data from one table to another table using the INSERT INTO SELECT statement.
- For using the INSERT INTO SELECT statement, the data types of the source and the destination tables have to be the same.
- The original data of the source table and the existing records of the destination tables are not affected.