SQL INSERT INTO Statement

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, SQL insert examples for real-world use cases, and the properties of INSERT statements in SQL that help you to insert data into SQL tables efficiently.  

Table of Contents: 

• What is the SQL INSERT INTO Statement?
• How to Insert a Single Row Using INSERT INTO in SQL?
• Inserting Multiple Rows into the Table
• SQL Bulk Insert with Transactions for Large Datasets
• How to Import Data into SQL from CSV Using LOAD DATA INFILE
• SQL INSERT INTO SELECT Statement to Copy Data Between Tables
• SQL INSERT INTO Specific Columns
• Using INSERT INTO SELECT to Copy Data
• Common Mistakes and Best Practices for SQL insert statements
• Real-World SQL INSERT INTO Examples for Orders, Students, and Transactions
• Conclusion

What is the SQL INSERT INTO Statement?

The SQL INSERT INTO statement is fundamental for adding 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.

SQL INSERT 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
);

How to Insert a Single Row Using INSERT INTO in SQL?

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. 

Mastering the SQL INSERT INTO Statement and Other Key Concepts

SQL Certification Training

Explore Program

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. 

SQL Bulk Insert with Transactions 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. 

How to Import Data into SQL from CSV Using LOAD DATA INFILE?

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.  

SQL INSERT INTO SELECT Statement to Copy Data Between Tables

The INSERT INTO….. SELECT statement in SQL is also used to insert a large set of data into a table. The INSERT INTO SELECT SQL method helps copy data between tables efficiently. 

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.

SQL INSERT INTO Specific Columns

You can also demonstrate how the SQL INSERT INTO specific columns command can be used, 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 for SQL INSERT Statements

• 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 SQL INSERT INTO Examples for Orders, Students, and Transactions

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.

Mastering SQL Key Concepts

SQL Free Training Course

Explore Program

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 into SQL table workflows smoothly and securely.

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.