When you want to generate data daily and store it properly in its respective table, you can use the INSERT statement in SQL, which helps you to insert new data into the table efficiently. This INSERT statement plays a very important role in managing and manipulating data in the table. In this blog, you will learn how to use INSERT statements properly and the properties of INSERT statements in SQL.
Table of Contents:
What is the SQL INSERT INTO Statement?
The INSERT INTO statement in SQL is a fundamental and most basic SQL command that is used to add new rows, data, and records into the tables. By specifying the table name, column names, and corresponding values, you can add Transactions and ensure data integrity. It will combine large sets of data and insert them into the table.
Syntax:
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
Creating Sample Data:
There are multiple ways to insert data into a table, each with specific conditions. Let’s create a table named Cars, and use the following methods to insert the values into the table.
CREATE TABLE Cars (
CarID INT PRIMARY KEY AUTO_INCREMENT,
Brand VARCHAR(100) NOT NULL,
Model VARCHAR(100) NOT NULL,
Year INT NOT NULL
);
Inserting a Single Row into the Table
To insert a single row into a table, you can use the INSERT INTO command. You need to specify values for each column in a single value clause.
Syntax:
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
Example:
INSERT INTO Cars (Brand, Model, Year)
VALUES ('Kia', 'Seltos', 2013);
SELECT * FROM Cars;
Output:
Explanation: Here, the INSERT INTO statement has inserted a single row with a single-line command.
Inserting Multiple Rows into the Table
You can insert multiple rows into a table in a single command. Multiple sets of values can be written within a single line command by separating them with commas.
Syntax:
INSERT INTO table_name (column1, column2, column3)
VALUES
(value1a, value2a, value3a),
(value1b, value2b, value3b);
Example:
INSERT INTO Cars (Brand, Model, Year)
VALUES
('T', 'Model 2', 2062),
('F', 'Must', 2093),
('B', 'X10', 2014);
SELECT * FROM Cars;
Output:
Explanation: Here, the INSERT INTO has added multiple rows in a single command.
Bulk Insert for Large Datasets
When you need to work with large datasets, you may need to insert a large set of rows efficiently. For that, you can’t use the simple INSERT statement. But in SQL, you can use Bulk Insert. The records will be wrapped inside transactions for data integrity, which improves the performance of the table.
Syntax:
BEGIN TRANSACTION;
INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');
COMMIT;
Example:
START TRANSACTION;
INSERT INTO Cars (Brand, Model, Year)
VALUES
('H', 'Civ', 2002),
('C', 'Cam', 2073),
('D', 'G6', 2004);
COMMIT;
SELECT * FROM Cars;
Output:
Explanation: Here, the START TRANSACTION and COMMIT make sure that all the rows are inserted correctly, which helps in increasing the efficiency of the table.
Loading Data from a File
The Loading from a file method is used to handle extremely large datasets. This can be used to import data from a file and can execute multiple INSERT statements.
Steps to run the query:
Step 1: Save the data file as a CSV file and save it into this file location /path/to/data.csv.
Step 2: Run this command in the MySQL command line.
mysql --local-infile=1 -u root -p
Step 3: Change the path location to your path location.
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE Cars
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'n'
IGNORE 1 ROWS;
SELECT * FROM Cars;
Output:
Explanation: Here, the Loading has combined a large set of data into a CSV file and then inserted the data into the table.
Using INSERT INTO … SELECT for Bulk Data Transfers
The INSERT INTO….. SELECT statement in SQL is also used to insert a large set of data into a table.
Example:
-- To create a table
CREATE TABLE NewAdmissions (
StudentID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100),
Age INT,
Grade VARCHAR(10)
);
INSERT INTO NewAdmissions (Name, Age, Grade)
VALUES
('Charith', 14, '8th'),
('Jeff', 15, '9th'),
('Kavin', 13, '7th');
CREATE TABLE Students (
StudentID INT PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100),
Age INT,
Grade VARCHAR(10)
);
INSERT INTO Students (Name, Age, Grade)
SELECT Name, Age, Grade FROM NewAdmissions WHERE Grade LIKE '8th';
-- To display
SELECT * FROM Students;
Output:
Explanation: Here, the INSERT INTO…SELECT statement is used for transferring the data from NewAdmissions to Students and filtering only students who are from ‘8th’ grade.
Inserting Values into Specific Columns
You can also insert values into a specific column while other columns will have existing data. It will specifically modify the relevant column.
Example:
CREATE TABLE Movies (
MovieID INT PRIMARY KEY AUTO_INCREMENT,
Title VARCHAR(100) NOT NULL,
Director VARCHAR(100) NOT NULL,
ReleaseYear INT,
Genre VARCHAR(50)
);
--Insert values into a specific column
INSERT INTO Movies (Title, Director)
VALUES ('Spyder', 'Parker');
SELECT * FROM Movies;
Output:
Explanation: Here, the INSERT INTO statement specifically inserted values into the title and director of the film and left the release year and genre columns empty by default, returning as NULL.
Using INSERT INTO SELECT to Copy Data
The INSERT INTO SELECT statement in SQL can be used to copy the data from one table to another table efficiently, which will reduce the time of manually inserting each value.
Syntax:
INSERT INTO target_table (column1, column2)
SELECT column1, column2 FROM source_table;
Example:
CREATE TABLE Products (
ProductID INT PRIMARY KEY AUTO_INCREMENT,
ProductName VARCHAR(100),
Category VARCHAR(100)
);
CREATE TABLE FeaturedProducts (
FeaturedID INT PRIMARY KEY AUTO_INCREMENT,
ProductName VARCHAR(100),
Category VARCHAR(100)
);
INSERT INTO Products (ProductName, Category)
VALUES
('Laptop', 'Electronics'),
('Bean Bag', 'Furniture'),
('AC', 'Electronics');
INSERT INTO FeaturedProducts (ProductName, Category)
SELECT ProductName, Category FROM Products WHERE Category = 'Electronics';
SELECT * FROM FeaturedProducts;
Output:
Explanation: Here, the SELECT command copies the product names from products to featured products.
Common Mistakes and Best Practices
- Avoid using the INSERT command without mentioning the column names because it may give an error, as there is a change in the structure of the table.
- You need to make sure that your data type strings match the present column in the table.
- If you insert a duplicate value in a primary key column, it will result in an error.
- You can use the transactions to insert large datasets. This will ensure data consistency.
- You can use the INSERT INTO SELECT command instead of a loop to have better performance.
Real-World Examples
Case 1: Order details: To get the order details and stock of the product in the factory.
Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
CustomerName VARCHAR(100) NOT NULL,
Product VARCHAR(100) NOT NULL,
Quantity INT NOT NULL,
OrderDate DATE NOT NULL
);
INSERT INTO Orders (CustomerName, Product, Quantity, OrderDate)
VALUES
('Buttler', 'Torch', 1, '2024-04-02'),
('Bas', 'Towel', 2, '2024-04-01'),
('Rasikh', 'Knife', 3, '2024-04-02');
SELECT * FROM Orders;
Output:
Explanation: Here, the INSERT INTO statement inserts the product and its quantity, along with the details of the order date.
Case 2: Student who enrolled in courses in Intellipaat: To get the details of students who enrolled in the courses at Intellipaat.
Examples:
CREATE TABLE StudentEnrollments (
EnrollmentID INT PRIMARY KEY,
StudentName VARCHAR(100),
CourseName VARCHAR(100),
EnrollmentDate DATE
);
INSERT INTO StudentEnrollments (EnrollmentID, StudentName, CourseName, EnrollmentDate)
VALUES
(2001, 'Tharun', 'Python', '2024-01-15'),
(2002, 'Akash', 'Machine Learning', '2024-01-20');
SELECT * FROM StudentEnrollments;
Output:
Explanation: Here, the INSERT INTO statement inserts the data into the student’s enrollment column.
Case 3: Transaction details: To get the transaction details from the bank.
Example:
CREATE TABLE Transactions (
TransactionID INT PRIMARY KEY AUTO_INCREMENT,
AccountNumber VARCHAR(20) NOT NULL,
TransactionType VARCHAR(10) NOT NULL,
Amount DECIMAL(10,2) NOT NULL,
TransactionDate DATETIME NOT NULL
);
INSERT INTO Transactions (AccountNumber, TransactionType, Amount, TransactionDate)
VALUES
('38579305', 'Deposit', 1000.00, '2024-04-02 10:30:00'),
('93846534', 'Withdrawal', 500.00, '2024-04-02 11:15:00'),
('48650265', 'Deposit', 200.00, '2024-04-02 12:00:00');
SELECT * FROM Transactions;
Output:
Explanation: Here, the INSERT INTO statement inserts the transaction details with the transaction date and amount. The SELECT statement displays the result.
Conclusion
The SQL INSERT INTO statement is the backbone of adding data to tables. It comes with a few different approaches, like single-row inserts, multiple inserts, bulk inserts, and even INSERT INTO SELECT, which lets you copy data from one table to another within the same database. These techniques are great for optimizing your data. The transactions play a crucial role in keeping your data intact by bundling large sets of data before they get inserted into the table. By learning these methods, you will be able to insert data efficiently into a table.
Take your skills to the next level with this SQL Training Course and gain hands-on experience. Also, prepare for job interviews with SQL interview questions prepared by industry experts.
Our SQL Courses Duration and Fees
Cohort Starts on: 15th Apr 2025
₹15,048
Cohort Starts on: 22nd Apr 2025
₹15,048