The INSERT Statement

Every row that ever ends up in StreamHub - a new user, a new movie, a new review - gets there through an INSERT statement. It's the first of the four core statements that make up everyday SQL work (the other three, SELECT, UPDATE, and DELETE, are next up in this track), and it's worth getting comfortable with its variations, because real applications rarely insert exactly one plain row at a time.

Basic INSERT Syntax

Here's a brand-new StreamHub user signing up:

INSERT INTO users (username, email, signup_date)
VALUES ('devika_reviews', 'devika@streamhub.example', '2026-08-16');

You name the columns you're supplying values for, then list matching values in the same order. MySQL fills every other column in the row based on its definition - which brings us straight to the next few tricks.

Inserting Multiple Rows at Once

When you have a batch of rows ready - say, StreamHub's initial set of genres - a single INSERT can take several value lists separated by commas:

INSERT INTO genres (name)
VALUES
  ('Action'),
  ('Comedy'),
  ('Drama'),
  ('Sci-Fi');

This is meaningfully faster than running four separate INSERT statements, because MySQL only has to parse the statement once and can write the rows as one batch instead of four separate round trips. When you're loading any real volume of data - importing a CSV of movies, seeding lookup tables - always prefer one multi-row insert over a loop of single-row inserts.

Omitting Columns With a Default or NULL

As covered in Constraints, a column with a DEFAULT or one that simply allows NULL doesn't need to appear in the insert at all:

-- signup_date has DEFAULT (CURRENT_DATE), so we can leave it out entirely
INSERT INTO users (username, email)
VALUES ('arjun_movies', 'arjun@streamhub.example');

MySQL fills signup_date with today's date automatically, exactly as designed.

Letting AUTO_INCREMENT Assign the Primary Key

Every StreamHub table so far has used an id column with AUTO_INCREMENT as its primary key. You never supply that column yourself - just leave it out, and MySQL assigns the next number:

INSERT INTO movies (title, release_year, duration_minutes)
VALUES ('Signal Lost', 2024, 118);

If the last movie inserted had id 41, this new row automatically becomes id 42 - no guessing, and no risk of two inserts colliding on the same id.

Getting the Id MySQL Just Assigned

Right after that insert, your application usually needs to know the new movie's id immediately - for example, to insert its genre links into movie_genres in the very next statement. LAST_INSERT_ID() returns exactly that, for the connection that just did the insert:

INSERT INTO movies (title, release_year, duration_minutes)
VALUES ('Signal Lost', 2024, 118);

-- Returns the id MySQL just assigned to the row above, e.g. 42
SELECT LAST_INSERT_ID();

-- Now link that movie to a genre using the id we just got back
INSERT INTO movie_genres (movie_id, genre_id)
VALUES (42, 1);

This is the standard pattern for populating a many-to-many junction table like movie_genres right after creating the row on one side of the relationship.

INSERT ... ON DUPLICATE KEY UPDATE (Upserts)

Sometimes you don't know in advance whether a row already exists - you just want to insert it if it's new, or update it if it isn't. Instead of checking first and then choosing between INSERT and UPDATE, MySQL lets you say both in one statement. Imagine StreamHub tracks one watchlist entry per user/movie pair and wants re-adding a movie to simply refresh its added_date instead of erroring on a duplicate:

INSERT INTO watchlist (user_id, movie_id, added_date)
VALUES (7, 42, '2026-08-16')
ON DUPLICATE KEY UPDATE added_date = '2026-08-16';

If no row exists yet for that user and movie, this behaves like a normal insert. If a unique or primary key conflict happens - the pair is already on the watchlist - MySQL runs the UPDATE clause on the existing row instead of raising an error. This "upsert" pattern is genuinely useful anywhere you're syncing data and don't want to write the check-then-insert-or-update logic by hand.

When an INSERT Violates a Constraint

Everything covered in Constraints applies in full force to every insert. Try to sneak an invalid rating into reviews:

INSERT INTO reviews (user_id, movie_id, rating, comment)
VALUES (7, 42, 9, 'Incredible show!');

MySQL rejects the entire statement - the row is never written, rating 9 and all. This is exactly the safety net constraints are meant to provide: no matter which INSERT variation you use above, the rules defined on the table still get the final say.