How to Insert Data into a Database with SQL
An in-depth guide to using the INSERT statement to add data to relational databases
Introduction
In the world of data-driven applications, interacting with databases is a fundamental task. One of the most common operations you’ll perform is inserting new data into a database. This is typically done using SQL (Structured Query Language), the industry-standard language for working with relational databases such as PostgreSQL, MySQL, SQLite, Oracle, and SQL Server.
In this guide, we’ll explore the SQL INSERT statement—how it works, when and why to use it, best practices, and common pitfalls. By the end, you’ll be able to confidently add new rows of data to your tables using SQL.
Learn SQL online
What Is the INSERT Statement?
The INSERT statement is used to add new records (also known as rows) into a table in a relational database. It allows you to specify which columns you are providing values for and what those values are.
Basic Syntax
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
table_name: The name of the table you’re inserting data into.(column1, column2, ...): A list of column names where the data will be inserted.(value1, value2, ...): A corresponding list of values for those columns.
The order of the values must match the order of the columns.
Practical Example: Inserting a User
Let’s suppose we have a users table defined as follows:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
In this example:
The
idcolumn is auto-incremented usingSERIAL.The
usernameandemailfields are required (NOT NULL).The
created_atfield automatically gets the current timestamp if no value is provided.
Now, let’s insert a new user into this table:
INSERT INTO users (username, email)
VALUES ('jdoe', 'jdoe@example.com');
We do not provide a value for
idbecause it auto-generates.We also omit
created_atbecause it has a default.
After execution, a new row will be added to the users table with a unique id and the current timestamp.
Inserting Multiple Rows
You can also insert multiple records in a single statement by providing multiple value sets:
INSERT INTO users (username, email)
VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com'),
('carol', 'carol@example.com');
Why Insert Multiple Rows?
Performance: Reduces the number of round-trips between your application and the database.
Atomicity: You can wrap the entire insert in a transaction for all-or-nothing behavior.
Inserting Data from Another Table
You can populate a table using data from another table. This is often used when archiving old records or transferring data.
INSERT INTO archived_users (id, username, email, created_at)
SELECT id, username, email, created_at
FROM users
WHERE created_at < '2024-01-01';
This example copies all users created before January 1, 2024, into an archive table.
Make sure the column names and data types in both tables match exactly, or the operation will fail.
Verifying Inserts
After inserting data, it’s common to check whether the operation succeeded.
SELECT * FROM users ORDER BY id DESC LIMIT 5;
his returns the 5 most recent users based on their id.
Common Mistakes and How to Avoid Them
| Problem | Description | Solution |
|---|---|---|
| Missing required fields | You omitted a NOT NULL column with no default | Always review table schema |
| Wrong data type | Inserted a string into an integer field, for example | Match data types exactly |
| Unique constraint violations | Duplicate values inserted into a unique column | Use ON CONFLICT or UPSERT strategies |
| Improper quoting | Forgetting to quote strings | Use proper SQL literals or parameterized queries |
Summary
Here’s a recap of the key points:
Use
INSERT INTOto add new rows to a table.Always specify column names in your insert statements.
Batch insert multiple rows for better performance.
Use parameterized queries to protect against SQL injection.
Understand your schema to avoid common errors with constraints and data types.
Please see the other SQL video learning articles below as part of this learning SQL online course video.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
