To insert data into a SQL table, you use the INSERT INTO statement. Here’s a breakdown of the syntax, common patterns, and how it fits into a Spring Data JPA project.
1. Basic Syntax
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
Example — inserting a user into a users table:
INSERT INTO users (name, email, age)
VALUES ('Alice', '[email protected]', 25);
2. Inserting Without Specifying Columns
If you provide values for every column in the exact order they’re defined in the table, you can omit the column list:
INSERT INTO users
VALUES (1, 'Alice', '[email protected]', 25);
Not recommended — if the schema changes, your query breaks. Always list columns explicitly.
3. Inserting Multiple Rows at Once
Most databases (MySQL, PostgreSQL, SQL Server) support multi-row inserts:
INSERT INTO users (name, email, age)
VALUES
('Alice', '[email protected]', 25),
('Bob', '[email protected]', 30),
('Carol', '[email protected]', 22);
This is much faster than running many separate INSERT statements.
4. Inserting from Another Table
You can copy rows from one table into another using INSERT INTO ... SELECT:
INSERT INTO archived_users (name, email, age)
SELECT name, email, age
FROM users
WHERE age < 18;
5. Handling Auto-Generated Columns
If a column is auto-generated (e.g., id BIGINT AUTO_INCREMENT PRIMARY KEY), simply omit it — the database will generate the value:
INSERT INTO users (name, email, age)
VALUES ('Dave', '[email protected]', 40);
6. Handling Duplicates
Different databases offer different ways to handle conflicts:
PostgreSQL — ON CONFLICT:
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Alice')
ON CONFLICT (email) DO NOTHING;
MySQL — ON DUPLICATE KEY UPDATE:
INSERT INTO users (email, name)
VALUES ('[email protected]', 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);
7. In a Spring Data JPA Project
In a project that uses Spring Data JPA, you usually don’t write INSERT statements directly. Instead:
Option A — Use the repository (recommended)
@Entity
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String email;
private int age;
// getters/setters or Lombok @Data
}
@Service
@RequiredArgsConstructor
public class UserService {
private final UserRepository userRepository;
public User createUser(String name, String email, int age) {
User user = new User();
user.setName(name);
user.setEmail(email);
user.setAge(age);
return userRepository.save(user); // Generates the INSERT for you
}
}
Option B — Native SQL with @Query
public interface UserRepository extends JpaRepository<User, Long> {
@Modifying
@Transactional
@Query(value = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)",
nativeQuery = true)
void insertUser(@Param("name") String name,
@Param("email") String email,
@Param("age") int age);
}
8. Best Practices
- Always list columns explicitly — makes queries resilient to schema changes.
- Use parameterized queries / prepared statements to prevent SQL injection.
- Batch inserts when loading large amounts of data.
- Wrap multiple inserts in a transaction for atomicity.
- Don’t insert into auto-generated columns manually unless you have a reason.
Summary
| Task | Statement |
|---|---|
| Insert one row | INSERT INTO t (cols) VALUES (...); |
| Insert multiple rows | INSERT INTO t (cols) VALUES (...), (...); |
| Copy from another table | INSERT INTO t (cols) SELECT ... FROM other; |
| Handle duplicates | ON CONFLICT (Postgres) / ON DUPLICATE KEY (MySQL) |