INSERT INTO in SQL

The INSERT INTO statement could be used in order to insert new records in any table. If we use the INSERT or the INSERT INTO statement, both of these will insert the data into any table. However, the INSERT INTO statement is basically used to fetch the data present in some other table using the select command and then insert it into the table where we want to insert the data.

In this article, we will dive deeper into the INSERT INTO in SQL according to the GATE Syllabus for (Computer Science Engineering) CSE. Keep reading ahead to learn more.

Table of Content

• What is INSERT INTO in SQL?
• Only Values
• Both Column Names and Values
• Using SELECT in the INSERT INTO Statement
  • Insertion of all the Columns of a Table
  • Insertion of Specific Columns of a Table
  • Copying of Specific Rows from Any Table

What is INSERT INTO in SQL?

To insert a new row into a table, you can use the SQL INSERT INTO statement. There are two ways to insert rows using the INSERT INTO statement:

1. Only Values

The first way is to specify only the value of the data to be put rather than the names of the columns.

Syntax:

INSERT INTO name_of_table VALUES (value_A, value_B, value_C,…);

Where,

name_of_table: the name of the table

value_A, value_B, value_C,.. : the value of the first, second, third column,… for the new record

2. Both Column Names and Values

We can specify both the columns we want to fill and their respective values in the second method, as described below:

Syntax:

INSERT INTO name_of_table (column_A, column_B, column_C, ..) VALUES ( value_A, value_B, value_C,…);

Where,

name_of_table: the name of the table

column_A, column_B, column_C, ..: name of first, second, third column …

value_A, value_B, value_C,.. : the value of the first, second, third column,… for the new record

Employee

Queries

First Method (Insertion of only values):

INSERT INTO Employee VALUES (‘D5′,’HARSH’,’CHHATTISGARH’,’0123456789’,’29’);

Output:

The table Employee would now look like this:

Second Method (Insertion of values only in specified columns):

INSERT INTO Employee (ID_NO, NAME, Age) VALUES (‘D5′,’PRATIK’,’29’);

Output:

The table Employee would now look like this:

Note: Notice that all those columns for which individual values aren’t provided are ultimately filled by null. The null here is the default value for those columns.

Using SELECT in the INSERT INTO Statement

We can copy rows from one table and insert them into another table using the SELECT query and the INSERT INTO statement. This statement is comparable to the INSERT INTO statement in terms of usage. The only difference is that in this case, the SELECT command is used to select data from a different table. The following are the various methods to use the INSERT INTO SELECT statement:

Insertion of all the Columns of a Table

We can copy all of the data from one table and paste it into another.

Syntax:

INSERT INTO table_A SELECT * FROM table_B;

Where,

table_A: the name of the first table

table_B: the name of the second table

The SELECT command was used to copy data from one table to another, while the INSERT INTO statement was used to insert data into a different table.

Insertion of specific Columns of a Table

Only the columns of a table that we want to insert into another table can be copied.

Syntax:

INSERT INTO table_A(names_of_columns_A) SELECT names_of_columns_B FROM table_B;

Where,

table_A: the name of the first table

table_B: the name of the second table

names_of_columns_A: the name of the columns that are separated by comma(,) for table A.

names_of_columns_B: the name of the columns that are separated by comma(,) for table B.

Here, we used the SELECT command to copy the data from the second table’s specific columns only, and the INSERT INTO query to insert it into the first table.

Copying of Specific Rows from Any Table

Using the WHERE clause with the SELECT statement, we can copy particular rows from one table and insert them into another. To select certain rows, we must employ appropriate conditions in the WHERE clause.

INSERT INTO table_A SELECT * FROM table_B WHERE condition;

table_A: the name of the first table

table_B: the name of the second table

condition: the condition to select the specific rows

Table B: LateralEmployee

Queries:

First Method (Insertion of all columns and rows):

INSERT INTO Employee SELECT * FROM LateralEmployee;

Output:

This query will populate the table Employee with all of the data from the table LateralEmployee. The Employee table will now look like this:

Second Method (Insertion of specific columns):

INSERT INTO Employee(ID_NO,NAME,Age) SELECT ID_NO, NAME, Age FROM LateralEmployee;

Output:

The data from the table LateralEmployee will be inserted into the Employee table’s columns ID NO, NAME, and Age, while the rest columns in the Employee table will be filled with null, which is the default value for the remaining fields. The Employee table will now look like this:

Selection of specific rows for insertion:

INSERT INTO Employee SELECT * FROM LateralEmployeeWHERE Age = 18;

Output:

Only the first row from table LateralEmployee will be inserted into table Employee using this query. The Employee’s table will now look like this:

Insertion of multiple rows in the table using a Single SQL Statement:

INSERT INTO name_of_table(Column_A, Column_B, Column_C, …)

VALUES (Value_A, Value_B, Value_C, …),

(Value_A, Value_B, Value_C, …),

(Value_A, Value_B, Value_C, …),

……………………….. ;

name_of_table: the name of the table

Column_A: the name of the first, second, third column …

Value_A, Value_B, Value_C: the value of the first, second, third column,… for every new row inserted

You’ll need to utilise several lists of values, each separated by a “,”. Each value list corresponds to the values that should be placed in each new row of the table. Values in the following list indicate that values should be added in the table’s next row.

Example:

The SQL statement given as follows would insert numerous rows in the Employee table.

Input:

INSERT INTO Employee(ID_NO, NAME,AGE,GRADE,PLACE) VALUES(D1,”AMIT KUMAR”,25,10,”DELHI”),

(D2,”GAURI RAO”,28,12,”BANGALORE”),

(D3,”MANAV BHATT”,27,11,”NEW DELHI”),

(D4,”RIYA KAPOOR”,20,5,”UDAIPUR”);

Output : Employee TABLE

This query would insert all the values in every successive row in the Employee table. So, the Employee table would look something like this:

Keep learning and stay tuned to get the latest updates on the GATE Exam along with Eligibility Criteria, GATE Syllabus for CSE (Computer Science Engineering), GATE CSE Notes, GATE CSE Question Paper, and more.

Also Explore,

• DML- Data Manipulation Language
• UPDATE Statement in SQL
• LOCK Table Statement in SQL
• COMMENTS in SQL
• CREATE Statement in SQL
• DELETE Statement in SQL
• DROP Statement in SQL
• GRANT and REVOKE in SQL