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)