How to Add New Data in SQL with INSERT INTO | Full Beginner’s Guide
Introduction
After creating a table in a database, the next important step is to
add new records. In SQL, we use the INSERT INTO statement to insert
data into a table.
This section will explain:
- Basic
INSERT INTO
syntax - How to insert single and multiple rows
- Best practices when adding new data
1. Basic Syntax of INSERT INTO
The basic form of an INSERT INTO statement looks like this:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
Example:
Suppose you have a table called students:
CREATE TABLE students (
id INT,
name VARCHAR(50),
age INT
);
To add a new student:
INSERT INTO students (id, name, age)
VALUES (1, 'John Doe', 20);
What Happens?
id
will be 1name
will be 'John Doe'age
will be 20
2. Inserting Multiple Rows at Once
You can insert multiple records in a single query by separating each set of values with a comma.
Syntax:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES
(value1a, value2a, value3a),
(value1b, value2b, value3b),
(value1c, value2c, value3c);
Example:
INSERT INTO students (id, name, age)
VALUES
(2, 'Jane Smith', 22),
(3, 'Alice Johnson', 19),
(4, 'Robert Brown', 21);
Benefits of inserting multiple rows:
- Saves time
- Reduces server load
- Improves performance
3. Inserting Data Without Specifying Column Names
You can omit the column list if you are inserting values for all columns in order
Syntax:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
Example:
INSERT INTO students
VALUES (5, 'Emily Davis', 23);
4. Handling NULL Values
If you don't know a value yet, you can insert NULL.
Example:
INSERT INTO students (id, name, age)
VALUES (6, 'Michael Scott', NULL);
5. Using DEFAULT Values
If your table has default values for some columns, you can skip those fields while inserting.
Example Table:
CREATE TABLE employees (
id INT,
name VARCHAR(50),
status VARCHAR(10) DEFAULT 'Active'
);
Inserting a record:
INSERT INTO employees (id, name)
VALUES (1, 'Olivia Martin');
Here, status will automatically be 'Active'.
Quick Tips for INSERT INTO:
- Always match the number of columns with the number of values.
- Use parameterized queries (in applications) to prevent SQL Injection attacks.
- Insert bulk data using multiple rows for better performance.
- Use transactions if you are inserting important data that must not be partially saved.
Conclusion
The INSERT INTO statement is your primary tool to add new data into SQL tables. Learning to use it efficiently is essential for data entry, application development, and database management.