In SQL, managing data effectively involves the ability to insert new rows into a table. TheINSERT INTO statement is used to add data to a database table. This statement provides two methods for inserting rows: inserting only values and inserting values with column names. Each method has its own syntax and practical use cases.
In this article, we will explain both methods in detail, provide examples, and show how the output table looks after each insertion.
1. Inserting Rows with Values Only
The first method of using the INSERT INTO statement is to specify only the values of the data to be inserted without mentioning the column names. This method is straightforward but lacks flexibility in specifying which columns receive the values. Inserting values this way is straightforward but does not allow us to specify which columns receive the values, making it ideal for quick and simple data insertion. The syntax is as follows:
Syntax:
INSERT INTO table_name VALUES (value1, value2, value3,...);
Key Terms
- table_name: name of the table.
- value1, value2,.. : value of first column, second column,... for the new record
Example of Inserting Rows with Values Only
The Student table is designed to store information about students enrolled in a school or university. It contains five columns: ROLL_NO, NAME, ADDRESS, PHONE, and Age. Each row in the table represents a unique student with easy access to each student's details and facilitates operations such as querying, updating, and deleting records.
To add a new student named HARSHfrom WEST BENGALwith a PHONE number 8759770477 and AGE19, we would use the following SQL query:
Query:
INSERT INTO Student VALUES ('5','HARSH','WEST BENGAL','8759770477','19');
Output
INSERT INTO Example 12. Inserting Rows with Specified Columns and Values
The second method of using theINSERT INTO statement involves specifying both the columns that need data and their corresponding values. This approach provides more control over which columns receive the data and is useful for inserting into partial tables where not all columns are included. The syntax is as follows:
Syntax:
INSERT INTO table_name (column1, column2, column3,..)
VALUES ( value1, value2, value3,..);
table_name: name of the table.
Key Terms
table_name: The name of the table where the new row will be inserted.
column1, column2, ...: The columns in the table that we want to fill with data.
value1, value2, value3, ...: The values corresponding to the specified columns in the order they appear in the table definition.
Example of Inserting Rows with Specified Columns and Values
Continuing with the Student table example, if we want to insert a new student with only the ROLL_NO, NAME, and AGE, and leave out ADDRESS and PHONE, we would use the following SQL query:
Query:
INSERT INTO Student (ROLL_NO, NAME, Age) VALUES ('5','HARSH','19');
Output
| ROLL_NO | NAME | ADDRESS | PHONE | Age |
|---|
| 1 | Ram | Delhi | 9455123451 | 18 |
| 2 | Ramesh | GURGAON | 9562431543 | 18 |
| 3 | Sujit | ROHTAK | 9156253131 | 20 |
| 4 | Suresh | Delhi | 9156768971 | 18 |
| 3 | Sujit | ROHTAK | 9156253131 | 20 |
| 2 | Ramesh | GURGAON | 9562431543 | 18 |
| 5 | HARSH | - | - | 19 |
Conclusion
Using the INSERT INTO statement effectively is essential for managing and manipulating data in SQL. Whether we are adding new rows with only values or specifying columns along with values, understanding these methods allows us to work efficiently with SQL tables. By using these methods, we can maintain data integrity, avoid errors, and keep your database organized. This article provides a clear, structured introduction to using theINSERT INTO statement, detailed explanations of both methods, practical examples with outputs, and a conclusion that reinforces the importance of these methods in SQL
Similar Reads
PL/SQL INSERT INTO
PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural extension to SQL. It allows us to write complex queries and scripts that include procedural logic, control structures, and error handling. The INSERT INTO statement in PL/SQL is essential for adding new rows of data to tab
6 min read
SQLite INSERT INTO SELECT
SQLite is a lightweight and server-less relational database management system. It requires very minimal configuration which has proven to be very helpful for developers to integrate it into any applications with ease. Due to its server-less architecture, we can use SQLite in various mobile applicati
4 min read
PL/SQL INSERT INTO SELECT
In PL/SQL, the INSERT INTO SELECT statement is used to insert data into a table by selecting data from one or more tables. This is a powerful feature for populating tables with data from existing tables or views, making it useful for data migration, reporting, and backup processes.In this guide, we
5 min read
MySQL INSERT INTO Statement
In MySQL, the INSERT INTO statement is essential for adding new data rows to a table in a database. This is important for setting up initial data in tables and for adding new records as needed when working with the database. Understanding how to use the INSERT INTO statement is key for managing and
6 min read
SQL Query to Insert Multiple Rows
In SQL, the INSERT statement is used to add new records to a database table. When you need to insert multiple rows in a single query, the INSERT statement becomes efficient.In this article, We will learn different methods such as using basic INSERT statements, utilizing INSERT INTO ... SELECT for bu
4 min read
SQL INSERT INTO Statement
The SQL INSERT INTO statement is one of the most commonly used commands for adding new data into a table in a database. Whether you're working with customer data, products, or user details, mastering this command is crucial for efficient database management. Let’s break down how this command works,
6 min read
SQL INSERT INTO SELECT Statement
In SQL, the INSERT INTO statement is used to add or insert records into the specified table. We use this statement to insert data directly into a table by specifying column names in a specific order. The SELECT statement is used to retrieve data from the table, and it can be used in conjunction with
5 min read
PostgreSQL - INSERT
PostgreSQL INSERT statement is one of the fundamental SQL commands used to add new rows to a specified table within a PostgreSQL database. This command allows users to insert data efficiently, whether for a single record or multiple records at once. With the PostgreSQL INSERT INTO clause, we can spe
4 min read
SQL Server INSERT Multiple Rows
SQL Server is a relational Database Management System(RDBMS). It offers various features for creating, and managing databases with its efficient tools. It can handle both small-scale and large-scale industry database applications. INSERT Statement in SQL ServerThe Insert statement is a command of th
4 min read
MySQL INSERT IGNORE
In MySQL, managing data insertion errors is crucial for maintaining data integrity and ensuring smooth database operations. The INSERT IGNORE statement provides a practical solution by allowing records to be inserted without interruption even if some rows would cause errors like duplicate key violat
5 min read