INSERT INTO in SQL

DevSniper
Published : 09 July 2024

177 Views

INSERT INTO is a fundamental statement of SQL. It is use to insert new records into the database table. It allows you to specify both database table in which you want to insert records and the columns along with their corresponding values.

Syntax

You can write INSERT INTO statement in two ways

1. Inserting Data Explicitly Specifying Columns

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN);

2. Inserting Data Without Specifying Columns

If you are adding values for all the columns of the database table, you do not need to specify the column names in the SQL query. But ensure that the values are arranged in the same order as the table's columns.

INSERT INTO table_name
VALUES (value1, value2, ..., valueN);

INSERT INTO is a fundamental SQL operation that is use to insert new records into the database table.
Although SQL allows you to insert data into a table without specifying column names, it is recommended to always specify the column names. This practice ensures that the query remains robust and reliable, even when new columns are added to the table, preventing potential issues or errors.
Using a single SQL query, you have the flexibility to insert both single records and multiple records into the table.
Inserting multiple rows with single INSERT INTO statement is generally more efficient than using separate statements for each row.
When inserting large amounts of data, it's recommended to use transactions to maintain atomicity and ensure data consistency.
Always validate data to ensure it matches column data types and constraints before insertion.
Use parameterized queries to mitigate SQL injection risks, especially when inserting data from user inputs.

Demo Database

CREATE TABLE Users (
    Id INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Mobile VARCHAR(15) UNIQUE NOT NULL,
    DOB DATE,
    Weight DECIMAL(5, 2)
);

Inserting a Single Row

INSERT INTO Users (Name, Mobile, DOB, Weight) 
VALUES ('Ram', '9999999999', '1997-07-05', 65);

Inserting Multiple Rows

INSERT INTO Users (Name, Mobile, DOB, Weight) 
VALUES ('Ram', '9999999999', '1993-01-05', 65),('Shyam', '9999999999', '1997-06-05', 55),
('Jack', '9999999999', '1995-07-05', 80);

Inserting Without Specifying Column Names

If you are not specifying column names in the INSERT INTO statement, you must provide values for all columns in the order they appear in the table schema.

INSERT INTO Users
VALUES ('Ram', '9999999999', '1993-01-05', 65);

INSERT INTO Users
 VALUES ('Ram', '9999999999', '1993-01-05', 65)	
Error Code: 1136. Column count doesn't match value count at row 1 0.000 sec

The error occurs because the number of values provided in the INSERT INTO statement does not match the number of columns in the Users table. Users table has 5 columns (Id, Name, Mobile, DOB, Weight) and we are passing 4 values. To resolve this error we have to provide values for all these columns in the correct order.

INSERT INTO Users
VALUES (NULL,'Ram', '9999999999', '1993-01-05', 65);
                OR
INSERT INTO Users
VALUES (70,'Ram', '9999999999', '1993-01-05', 65);

We have to explicitly provide NULL for the Id column because it is auto-incremented and will be assigned a unique value by MySQL OR can provide expected Id value.