SQL INSERT INTO Statement

SQL INSERT INTO statement adds data to the newly created tables or existing tables. We can add single records or multiple records in a table by using this query.

There are two possible ways to add data to a table:

• Mention the field's name and the values are added to the table.
• If we add values for all the table fields, we do not need to mention the field's name in the SQL INSERT INTO statement or query. But, make sure the order of the values is the same as the order of the columns in the table.

Syntax for SQL INSERT INTO statement by mentioning the fields names in the statement or query:  

INSERT INTO Table_Name (Column_Name1, Column_Name2, Column_Name3, Column_Name4, Column_Name5) VALUES (Value1, Value2, Value3, Value4, Value5);

Column_Name1, Column_Name2, Column_Name3, Column_Name4, Column_Name5 are the fields name in the tables into which we want to add values.

The syntax for SQL INSERT INTO statement without mentioning the names of the fields in the statement or query:  

INSERT INTO Table_Name VALUES (Value1, Value2, Value3, Value4, Value5);

The above syntax is used to insert values in all the fields of the tables.

The following statements would create eight records in the Customer table.

INSERT INTO Customer (Customer_Id, Customer_Name, Age, Address, Salary)

VALUES (1, 'Rakesh', 32, 'Ahmedabad', 20000);

INSERT INTO Customer (Customer_Id, Customer_Name, Age, Address, Salary)

VALUES (2, 'Kamlesh', 27, 'Delhi', 15000);

INSERT INTO Customer (Customer_Id, Customer_Name, Age, Address, Salary)

VALUES (3, 'kaustubh', 25, 'Pune', 20000);

INSERT INTO Customer (Customer_Id, Customer_Name, Age, Address, Salary)

VALUES (4, 'Chaitali', 25, 'Mumbai', 15000);

INSERT INTO Customer (Customer_Id, Customer_Name, Age, Address, Salary)

VALUES (5, 'Himesh', 29, 'Delhi', 45000);

INSERT INTO Customer (Customer_Id, Customer_Name, Age, Address, Salary)

VALUES (6, 'Komal', 22, 'MP', 45000);

INSERT INTO Customer (Customer_Id, Customer_Name, Age, Address, Salary)

VALUES (7, 'Nikhlesh', 28, 'Delhi', 40000);

INSERT INTO Customer (Customer_Id, Customer_Name, Age, Address, Salary)

VALUES (8, 'Kamolika', 24, 'Pune', 50000);

In the above INSERT INTO statement, we have added the records in the Customer table by mentioning the table's field names.

We can add the data to the table without mentioning the field's name of the table:

The following example statements would create six records in the Customer table.

INSERT INTO Customer VALUES (9, ‘Raman’, 30, ‘Mumbai’, 35500);

INSERT INTO Customer VALUES (10, ‘Manoj’, 40, ‘Pune’, 45000);

INSERT INTO Customer VALUES (11, ‘Shweta’, 26, ‘MP’, 42500);

INSERT INTO Customer VALUES (12, ‘Shivani’, 25, ‘Delhi’, 50000);

INSERT INTO Customer VALUES (13, ‘Rahul’, 28, ‘Nashik’, 34000);

INSERT INTO Customer VALUES (14, ‘Sahil’, 22, ‘Nashik’, 27000);

In the above INSERT INTO statement example, we have added the records without mentioning the fields name in the query.

All the above queries would generate the following data in the Customer table as shown below:

Customer_Id	Customer_Name	Age	Address	Salary
1	Rakesh	32	Ahmedabad	20000
2	Kamlesh	27	Delhi	15000
3	Kausubh	25	Pune	20000
4	Chaitali	25	Mumbai	15000
5	Himesh	29	Delhi	45000
6	Komal	22	MP	45000
7	Nikhlesh	28	Delhi	40000
8	Kamolika	24	Pune	50000
9	Raman	30	Mumbai	35500
10	Manoj	40	Pune	45000
11	Shweta	26	MP	42500
12	Shivani	25	Delhi	50000
13	Rahul	28	Nashik	34000
14	Sahil	22	Nashik	27000

INSERT INTO SELECT Statement.

INSERT INTO SELECT statement is also a way to add records to the table. INSERT INTO SELECT statement is used to insert records into one table from the existing table. Where all the fields and order of the fields are the same.

We will create another Customer and use the same fields from the above Customer table.

CREATE TABLE Customers (Customer_Id int Primary key, Customer_Name varchar(40), Age int, Address Varchar(20), Salary int);

Use the DESC command followed by the Customers table name to show the table structure.

Fields	Type	Null	Key	Default	Extra
Customer_Id	Int(11)	NO	PRI	NULL
Customer_Name	Varchar(40)	YES		NULL
Age	Int(11)	YES		NULL
Address	Varchar(20)	YES		NULL
Salary	Int(11)	YES		NULL

We can add records into a table through the SELECT statement over another table.

Syntax of the INSERT INTO SELECT:

INSERT INTO Table1 [(Column_Name1, Column_Name2, Column_Name3, Column_Name4, Column_Name5)] SELECT Column_Name1, Column_Name2, Column_Name3, Column_Name4, Column_Name5 FROM Table2;

Example of the INSERT INTO SELECT statement:

INSERT INTO Customers SELECT * FROM Customer;

In the above query INSERT INTO SELECT example, all the records from the Customer table are added to the Customers table.

The following data in the Customer table is shown below:

Customer_Id	Customer_Name	Age	Address	Salary
1	Rakesh	32	Ahmedabad	20000
2	Kamlesh	27	Delhi	15000
3	Kausubh	25	Pune	20000
4	Chaitali	25	Mumbai	15000
5	Himesh	29	Delhi	45000
6	Komal	22	MP	45000
7	Nikhlesh	28	Delhi	40000
8	Kamolika	24	Pune	50000
9	Raman	30	Mumbai	35500
10	Manoj	40	Pune	45000
11	Shweta	26	MP	42500
12	Shivani	25	Delhi	50000
13	Rahul	28	Nashik	34000
14	Sahil	22	Nashik	27000