INSERT INTO in SQL
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 secThe 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.
Frequently Asked Questions
Q1. What is the purpose of INSERT INTO?
The INSERT INTO statement is used to add new rows (records) into a database table. You specify either all or selected columns and provide values that align to those columns.
Q2. Can I insert multiple rows in a single INSERT INTO statement?
Yes. Many databases allow insertion of multiple rows by providing multiple sets of values separated by commas.
Here’s the typical syntax (works in systems such as PostgreSQL, MySQL, Microsoft SQL Server etc.)
INSERT INTO TableName (Column1, Column2, …)
VALUES
(Value1_Row1, Value2_Row1, …),
(Value1_Row2, Value2_Row2, …),
…;INSERT INTO Contacts (FirstName, LastName, Email)
VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com'),
('Bob', 'Johnson', 'bob.johnson@example.com');Q3. Can I insert data from one table into another using INSERT INTO?
Yes , in SQL you can insert data from one table into another using the INSERT INTO … SELECT statement. This is a common pattern when you want to copy or move rows from a “source” table into a “target” table.
1. Insert all columns from source to targetINSERT INTO TargetTable SELECT * FROM SourceTable;
INSERT INTO TargetTable (ColA, ColB, ColC) SELECT Col1, Col2, Col3 FROM SourceTable;
INSERT INTO TargetTable (ColA, ColB, ColC) SELECT Col1, Col2, Col3 FROM SourceTable WHERE <condition>;
Q4. Do I always have to list the column names in INSERT INTO?
No , you only need to list the column names if you are inserting values for some of the columns. If you omit the column list, you must supply values for all columns, in the same order as they are defined.
Q5. Are there best practices when using INSERT INTO?
Yes , following a few smart habits helps make your inserts safe, clear and performant:
- Always specify the column names in your INSERT statement so your code is resilient to schema changes.
- Ensure that data types, nullability and constraints align with the values you’re inserting. this prevents runtime errors and inconsistent data.
- Use batch or multi-row inserts for inserting many rows, rather than many single inserts, to reduce overhead and improve performance.
- Validate input data (check for duplicates, enforce rules, use transactions) so your insert logic doesn’t leave behind bad or partial data.
- If schema evolves (new columns, deprecated fields), revisit your insert logic so it remains accurate and maintainable.
- On large volumes, monitor and optimize for logging, locking, recovery model (e.g., minimal logging in SQL Server) to avoid performance bottlenecks.
- Avoid using SELECT * in INSERT … SELECT unless you’re absolutely sure schemas align and won’t change , prefer explicit column lists.