Insert One or More rows

In this article, we will learn how to insert one or more records in the table of Structured Query Language.

The INSERT INTO command in SQL allows the database users to insert one or more records into the SQL table.

Following are the two syntaxes for inserting the single row in the table:

Syntax 1:

INSERT INTO Table_Name(Column_Name_1, Column_Name_2, Column_Name_3, ....., Column_Name_N) VALUES ( Value_1, Value_2, Value_3, ....., Value_N);

This syntax inserts the row in the SQL table by specifying the column names of the table.

Syntax 2:

INSERT INTO Table_Name VALUES( Value_1, Value_2, Value_3, ....., Value_N);

In this syntax, we have no need to specify the column names for inserting the row into the SQL table.

If you want to insert the record into the table, you have to follow the following steps one by one in the given sequence:

Create the Database in the system.
Create the Table in the database.
Insert one row into the table.
View the table.

Now, we are going to explain each step with an example:

Step 1: Create a Database

In the Structured Query Language, creating a database is the first step for storing the structured tables in the database.

Use the following SQL syntax to create a database:

CREATE DATABASE Database_Name;

Suppose you want to create the School database. For this, you have to type the following command in Structured Query Language:

CREATE DATABASE School;

Step 2: Create a Table and Insert the data

Now, you have to use the following syntax for creating the table in the SQL database:

CREATE TABLE table_name
(
column_Name_1 data type (size of the column_1),
column_Name_2 data type (size of the column_2),
column_Name_3 data type (size of the column_3),
...
column_Name_N data type (size of the column_N)
);

Suppose you want to create the Teacher table in the School database. For this, you have to type the following query in your SQL application:

CREATE TABLE Teacher
(
Teacher_ID INT AUTO_INCREMENT PRIMARY KEY,
Teacher_Name VARCHAR (80),
Teacher_Subject VARCHAR (30) NOT NULL,
Teacher_Address VARCHAR (30),
Teacher_Age INT
) ;

Step 3: Insert one row into the table

The following query inserts the record of only one teacher into the Teacher table:

INSERT INTO Table_Name (Teacher_ID, Teacher_Name, Teacher_Subject, Teacher_Address, Teacher_Age) VALUES( 2001, Manoj, Maths, Delhi, 27);

Step 4: View the table

If you want to view the data of the Teacher table, then you have to write the following query in your SQL application:

SELECT * FROM Teacher;

Teacher_ID	Teacher_Name	Teacher_Subject	Teacher_Address	Teacher_Age
2001	Manoj	Maths	Delhi	27

Table: Teacher

Insert Multiple Rows into the table

In SQL, you can easily insert more than one row in the table using a single query.

The following Insert Into statement inserts the record of five teachers into the Teacher table:

INSERT INTO Table_Name (Teacher_ID, Teacher_Name, Teacher_Subject, Teacher_Address, Teacher_Age) VALUES( 2002, Anita, Hindi, Ghaziabad, 28),
( 2003, Vishal, English, Gorakhpur, 26),
( 2004, Shobhit, SST, Kanpur, 27),
( 2005, Rohit, Science, Lucknow, 29),
( 2006, Yogesh, Computer, Jaipur, 28) ;

To check the result of the above INSERT query, you have to write the following SELECT query in SQL:

SELECT * FROM Teacher;

Output:

Teacher_ID	Teacher_Name	Teacher_Subject	Teacher_Address	Teacher_Age
2001	Manoj	Maths	Delhi	27
2002	Anita	Hindi	Ghaziabad	28
2003	Vishal	English	Gorakhpur	26
2004	shobhit	SST	Kanpur	27
2005	Rohit	Science	Lucknow	29
2006	Yogesh	Computer	Jaipur	28

Table: Teacher

Insert Rows from the SELECT statement

If you want to insert the data of another table in the new table, use the INSERT statement with the SELECT statement.

The following statement passes the data of one table into another table:

INSERT INTO New_Table_Name (Column_Name_1, Column_Name_2, Column_Name_3, ....., Column_Name_N)
SELECT
(Column_Name_1, Column_Name_2, Column_Name_3, ....., Column_Name_N)
FROM Old_Table_Name
WHERE [ condition ];

Example

The data of the old table is shown in the following table:

Teacher_ID	Teacher_Name	Teacher_Subject	Teacher_Address	Teacher_Age
2001	Manoj	Maths	Delhi	27
2002	Anita	Hindi	Delhi	28
2003	Vishal	English	Gorakhpur	26
2004	shobhit	SST	Delhi	27
2005	Rohit	Science	Lucknow	29
2006	Yogesh	Computer	Delhi	28

Table: Old_Teacher

Suppose you have a New_Teacher table whose structure is the same as the Old_Teacher table, and you want to transfer the data of those teachers into the new table from the old table whose address is Delhi. For this operation, you have to use the following INSERT statement with the SELECT query:

INSERT INTO New_Teacher (New_Teacher_ID, New_Teacher_Name, New_Teacher_Subject)
SELECT Teacher_ID, Teacher_Name, Teacher_Subject
FROM Old_Teacher
WHERE Teacher_Address = 'Delhi' ;

To check the result of the above query, use the following SELECT statement:

SELECT * FROM New_Teacher;

Teacher_ID	Teacher_Name	Teacher_Subject
2001	Manoj	Maths
2002	Anita	Hindi
2004	shobhit	SST
2006	Yogesh	Computer

Table: New_Teacher

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz