SQL-SQL-INSERT-Guide
Master data insertion in SQL databases. Learn different ways to add records to your tables efficiently and safely.
You'll Learn
- Basic INSERT syntax
- Inserting multiple rows
- INSERT with SELECT
- Best practices and constraints
The Most Beautiful Database IDE You've Been Looking For
Get early access to our beta version and help us shape the future of SQL learning.
1. Basic INSERT Syntax
Let's start with a simple table structure:
users table structure
Column | Type | Constraints -----------+-------------+------------ id | INT | PRIMARY KEY username | VARCHAR(50) | UNIQUE email | VARCHAR(100)| NOT NULL created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP
Single Row Insert
INSERT INTO users (username, email)
VALUES ('john_doe', 'john@example.com');Key Point
When columns have default values or allow NULL, you don't need to include them in your INSERT statement.
2. Multiple Row Insert
Inserting Multiple Records
INSERT INTO users (username, email) VALUES
('jane_smith', 'jane@example.com'),
('bob_jones', 'bob@example.com'),
('alice_wong', 'alice@example.com');Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0
Performance Tip
Inserting multiple rows in a single statement is more efficient than executing multiple single-row INSERTs.
3. INSERT with SELECT
You can insert data from one table into another using SELECT:
INSERT INTO active_users (user_id, username, email)
SELECT id, username, email
FROM users
WHERE last_login > DATE_SUB(NOW(), INTERVAL 30 DAY);Key Point
The number and types of columns in the SELECT statement must match the columns specified in the INSERT.
4. Handling Duplicates
INSERT IGNORE
INSERT IGNORE INTO users (username, email)
VALUES ('john_doe', 'new_john@example.com');INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO users (username, email)
VALUES ('john_doe', 'new_john@example.com')
ON DUPLICATE KEY UPDATE
email = VALUES(email);Important
Choose between IGNORE and ON DUPLICATE KEY UPDATE based on your needs. IGNORE silently skips errors, while ON DUPLICATE KEY UPDATE allows you to specify how to handle conflicts.
5. Best Practices
INSERT Guidelines
- Always specify column names explicitly
- Use prepared statements for dynamic values
- Batch multiple inserts when possible
- Consider using transactions for multiple inserts