【SQL INSERT Tutorial】How to Add New Data to a Table with the INSERT INTO Statement
User registrations on a website, new blog posts, product listings on an e-commerce site… Web applications come alive because new data is constantly being created. The fundamental and crucial operation of "registering new data into a database" is handled by SQL's INSERT statement.
The INSERT statement is a command for writing a new row into the massive ledger that is a database. Its syntax may seem simple at first, but it offers a variety of uses, from adding a single row to bulk-inserting multiple rows, and even copying data from another table. However, because of its simplicity, a small mistake can easily lead to an error, making it a common stumbling block for beginners.
This article will thoroughly explain everything you need to master the INSERT statement, from basic syntax to practical applications, as well as frequent errors and their solutions, all accompanied by plenty of "copy-paste-and-run" code. Let's take the first step into the world of SQL by running the code and feeling the satisfaction of "data being added!"
Preparation: Let's Get a Table Ready for Data
To add data, you first need a "box" to put it in—that is, a table. This time, let's create a table called products to manage information about new products. We'll be using this table throughout the article.
Pay attention to the id column's INTEGER PRIMARY KEY. In many databases (like SQLite, or using AUTO_INCREMENT in MySQL), this setting automatically assigns a unique ID every time you add data. This will become important later.
-- Drop the products table if it exists (for repeatable testing)
DROP TABLE IF EXISTS products;
-- Create a new products table
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price INTEGER,
release_date DATE
);
With that, we have an empty table ready for product data. Let's start breathing some new life into this table right away.
【The Basics】INSERT INTO ... VALUES: Adding a Single Row of Data
First, let's look at the basic form of the INSERT statement: "adding a single row of data." The syntax is INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);. The key is that the column list in the first set of parentheses corresponds one-to-one with the value list after VALUES.
INSERT INTO products (name, category, price, release_date)
VALUES ('Miracle Apple', 'Groceries', 500, '2025-07-01');
When you run this query, one new product is added to the products table. We didn't specify the id column, but since we configured it to auto-generate numbers when creating the table, the database automatically assigns it the value 1. This is a major advantage of using an auto-incrementing primary key.
Let's check if it was added. If you run SELECT * FROM products;, you should see the data you just inserted.
SELECT * FROM products;Result:
id | name | category | price | release_date
---|---------------|-----------|-------|-------------
1 | Miracle Apple | Groceries | 500 | 2025-07-01Furthermore, if the values for some columns are not yet decided (limited to columns that allow NULL), you can omit those columns when inserting.
-- Insert by omitting price and release_date
INSERT INTO products (name, category)
VALUES ('Phantom Peach', 'Groceries');
In this case, price and release_date will contain NULL (no value).
【Application 1】Adding Multiple Rows of Data at Once
Registering new products one by one can be a bit tedious. With the INSERT statement, you can add multiple pieces of data all at once in a single query.
The syntax is simple: just list multiple value lists enclosed in () after the VALUES keyword, separated by commas (,). This is more efficient than repeating the INSERT statement for each row, as it only requires one round trip to the database.
INSERT INTO products (name, category, price, release_date) VALUES
('Magic Keyboard', 'Gadgets', 12000, '2025-07-10'),
('Rainbow Mouse', 'Gadgets', 5000, '2025-07-20'),
('Sky Grapes', 'Groceries', 2500, '2025-08-01');
With this single query, three product records have been added at once. This is a very useful technique for tasks like populating initial data or registering data loaded from a CSV file in bulk.
【Application 2】Copying Data from Another Table with `INSERT ... SELECT`
This is a very powerful technique for intermediate users. The INSERT ... SELECT statement allows you to take the result of a SELECT query from another table and insert it directly into a new table.
This is extremely useful in scenarios like moving products from a "new arrivals list" table (new_arrivals) to the main products table once their release has been confirmed.
First, let's create the source table, new_arrivals, and populate it with some data.
-- Create the source table
CREATE TABLE new_arrivals (
product_name TEXT NOT NULL,
product_category TEXT,
list_price INTEGER,
launch_date DATE,
is_confirmed INTEGER -- 1 if the release is confirmed, 0 otherwise
);
-- Populate the new arrivals list
INSERT INTO new_arrivals VALUES
('Ultimate Headphones', 'Audio', 28000, '2025-09-01', 1),
('Future VR Goggles', 'Gadgets', 75000, '2025-10-01', 1),
('Fully-Auto Coffee Maker', 'Appliances', 18000, '2025-09-15', 0);
We're all set. Now, let's select only the products from the new_arrivals table that are confirmed for release (is_confirmed = 1) and add them to our products table.
INSERT INTO products (name, category, price, release_date)
SELECT product_name, product_category, list_price, launch_date
FROM new_arrivals
WHERE is_confirmed = 1;
The key point of this query is that a SELECT statement is used instead of the VALUES clause. The column structure of the result set from the SELECT statement (number of columns, order, and data types) must match the column structure specified in INSERT INTO products (...). This has a wide range of uses, such as creating data backups or saving aggregated results to another table.
Common INSERT Errors and How to Fix Them
The INSERT statement will immediately return an error if you violate any of the database's rules (constraints). Let's look at some of the most common errors beginners encounter, their causes, and how to fix them.
Error 1: Primary Key Violation
This is the most common error. A primary key is a unique identifier within a table that must never be duplicated. If you are manually specifying IDs and try to use an ID that already exists, you will get an error.
For example, the "Miracle Apple" with ID=1 is already registered. If we try to register another product with ID=1...
-- This will cause an error!
INSERT INTO products (id, name, category, price, release_date)
VALUES (1, 'Another Apple', 'Groceries', 300, '2025-07-02');
Running this will display an error message like UNIQUE constraint failed: products.id or Duplicate entry '1' for key 'PRIMARY'.
Solution: The safest and most reliable approach is to not manage primary keys manually but to let the database's auto-increment feature handle it. As in our first example, simply omit the id from the column list in your INSERT statement.
Error 2: NOT NULL Constraint Violation
Columns specified with NOT NULL when the table was created must always have a value. Attempting to INSERT a row while omitting such a column will result in an error.
The name column in our products table is NOT NULL. If we try to insert without specifying a name...
-- This will also cause an error!
INSERT INTO products (category, price)
VALUES ('Other', 1000);
You will see an error like NOT NULL constraint failed: products.name.
Solution: Always provide some value for columns with a NOT NULL constraint. If you want to allow an empty string, you must specify it explicitly, like ''.
Error 3: Data Type Mismatch
You will get an error if you try to insert data of a type different from what was defined for the column. For example, trying to insert a string into the price column, which is an integer type (INTEGER).
-- This will of course cause an error!
INSERT INTO products (name, price)
VALUES ('Expensive Rock', 'Price is a secret');
This will produce an error like Datatype mismatch or Incorrect integer value: 'Price is a secret' for column 'price'.
Solution: Check your table definition carefully and provide values of the correct data type for each column. Be careful of common mistakes like wrapping numbers in quotes (e.g., '500'), which makes them strings.
【Interactive Playground】Let's Add Data by Running SQL in the Browser!
Now it's time to turn knowledge into a skill! Copy the entire HTML code below, save it as a file like sql_insert_test.html, and open it in your browser. Your own private SQL environment will launch, with the products table we created in this article ready to go.
Feel free to write your own INSERT statement and press "Execute," then write SELECT * FROM products; right below it and press "Execute" again to confirm that the data was really added. Intentionally causing errors is also a great way to learn!
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>SQL INSERT Statement Online Playground</title>
<style>
body { font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", Arial, sans-serif; line-height: 1.7; color: #333; max-width: 800px; margin: 2rem auto; padding: 0 1rem; }
h1 { color: #2c3e50; }
textarea { width: 100%; height: 180px; font-family: "SF Mono", "Consolas", monospace; font-size: 16px; padding: 12px; border: 1px solid #ccc; border-radius: 6px; box-sizing: border-box; margin-bottom: 1rem; }
button { background-color: #27ae60; color: white; border: none; padding: 12px 22px; font-size: 16px; border-radius: 6px; cursor: pointer; transition: background-color 0.2s; }
button:hover { background-color: #229954; }
button:disabled { background-color: #bdc3c7; cursor: not-allowed; }
#result-container { margin-top: 2rem; border: 1px solid #ddd; padding: 1rem; border-radius: 6px; background: #fdfdfd; min-height: 50px; }
#status-message { color: #27ae60; font-weight: bold; }
#error-message { color: #e74c3c; font-weight: bold; }
table { border-collapse: collapse; width: 100%; margin-top: 1rem; }
th, td { border: 1px solid #ddd; padding: 10px; text-align: left; }
th { background-color: #f2f2f2; }
tr:nth-child(even) { background-color: #f9f9f9; }
</style>
<script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/sql-wasm.js"></script>
</head>
<body>
<h1>Let's Try SQL!</h1>
<p>Enter an SQL statement in the textarea below and click the "Execute" button. Try inserting a product you made up!</p>
<textarea id="sql-input">-- Write your INSERT or SELECT statements here
INSERT INTO products (name, category, price, release_date)
VALUES ('Zero-Gravity Chair', 'Furniture', 45000, '2025-11-11');
SELECT * FROM products;
</textarea>
<button id="execute-btn">Execute</button>
<div id="result-container">
<p id="status-message"></p>
<p id="error-message"></p>
<div id="result-output"></div>
</div>
<script>
const sqlInput = document.getElementById('sql-input');
const executeBtn = document.getElementById('execute-btn');
const statusMsg = document.getElementById('status-message');
const errorMsg = document.getElementById('error-message');
const resultOutput = document.getElementById('result-output');
let db;
async function initDb() {
executeBtn.disabled = true;
executeBtn.textContent = 'DB Initializing...';
try {
const SQL = await initSqlJs({
locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/${file}`
});
db = new SQL.Database();
const setupSql = `
DROP TABLE IF EXISTS products;
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price INTEGER,
release_date DATE
);
`;
db.run(setupSql);
executeBtn.disabled = false;
executeBtn.textContent = 'Execute';
statusMsg.textContent = 'Ready! Try adding data with an INSERT statement and check the result with a SELECT statement.';
} catch (err) {
errorMsg.textContent = 'Failed to initialize database: ' + err.message;
console.error(err);
}
}
function executeSql() {
if (!db) return;
const sql = sqlInput.value;
statusMsg.textContent = '';
errorMsg.textContent = '';
resultOutput.innerHTML = '';
try {
const statements = sql.split(';').filter(s => s.trim() !== '');
let lastResult;
statements.forEach(stmt => {
const trimmedStmt = stmt.trim();
if (trimmedStmt.toUpperCase().startsWith('INSERT') || trimmedStmt.toUpperCase().startsWith('UPDATE') || trimmedStmt.toUpperCase().startsWith('DELETE')) {
db.run(trimmedStmt);
const changes = db.getRowsModified();
statusMsg.innerHTML += `Query "${trimmedStmt.substring(0, 30)}..." executed, ${changes} row(s) modified.<br>`;
} else {
const results = db.exec(trimmedStmt);
lastResult = results;
}
});
if (lastResult && lastResult.length > 0) {
lastResult.forEach(result => {
const table = document.createElement('table');
const thead = document.createElement('thead');
const tbody = document.createElement('tbody');
const headerRow = document.createElement('tr');
result.columns.forEach(colName => {
const th = document.createElement('th');
th.textContent = colName;
headerRow.appendChild(th);
});
thead.appendChild(headerRow);
result.values.forEach(row => {
const bodyRow = document.createElement('tr');
row.forEach(cellValue => {
const td = document.createElement('td');
td.textContent = cellValue === null ? 'NULL' : cellValue;
bodyRow.appendChild(td);
});
tbody.appendChild(bodyRow);
});
table.appendChild(thead);
table.appendChild(tbody);
resultOutput.appendChild(table);
});
}
} catch (err) {
errorMsg.textContent = 'SQL Error: ' + err.message;
console.error(err);
}
}
executeBtn.addEventListener('click', executeSql);
initDb();
</script>
</body>
</html>Friends of INSERT: CRUD Operations
Now that you've learned INSERT, you have acquired one of the four fundamental powers of database manipulation. These four operations are known by the acronym CRUD, and they form the basis of data operations in all applications.
- C (Create): Create new data →
INSERTstatement (which we learned today!) - R (Read): Read data →
SELECTstatement - U (Update): Update existing data →
UPDATEstatement - D (Delete): Delete existing data →
DELETEstatement
You're already familiar with SELECT. Now that you can "add" data, your next steps could be learning how to "update" (UPDATE) or "delete" (DELETE) it, which will further expand the range of what you can do with a database.
Conclusion
In this article, we've taken a detailed look at the INSERT statement for adding new data to a database, covering everything from the basics to advanced applications and common errors.
- Basic Syntax:
INSERT INTO table (columns...) VALUES (values...);is the foundation of everything. - Primary Keys: Setting them to auto-increment lets the database manage IDs safely and reliably.
- Multi-row INSERT: Chaining multiple value lists with commas after the
VALUESclause allows for efficient bulk registration. - INSERT SELECT: A powerful technique for adding data wholesale from another table.
- Error Handling: The big three are "Primary Key Violation," "NOT NULL Violation," and "Data Type Mismatch." They aren't scary once you understand the cause.
The INSERT statement is the first step toward breathing life into your websites and applications, recording user actions, and enriching your content. Practice again and again in this article's playground to get a feel for adding data freely. This accumulation of small successes will surely guide you on your path to becoming a top-notch web creator.