INSERT INTO

So far we have been reading data. Now let us talk about adding new data to a table. That is what INSERT is for.

Basic INSERT

The simplest way to insert a row:

INSERT INTO students VALUES (8, 'Ibrahim', 26, 'Jos', 91);

This inserts values in the order the columns were defined when the table was created. You must provide a value for every column. Miss one, and SQL complains.

Specifying Columns

Better approach — specify which columns you are inserting into. This way you can skip columns (they get NULL) and you do not need to remember the column order:

INSERT INTO students (name, age, city, score) 
VALUES ('Maryam', 23, 'Ibadan', 87);

-- If you want to leave out city:
INSERT INTO students (name, age, score) 
VALUES ('Kabir', 22, 76);

Inserting Multiple Rows

You can insert many rows in a single statement:

INSERT INTO students (name, age, city, score) VALUES 
  ('Grace', 24, 'Enugu', 93),
  ('Samuel', 20, 'Jos', 81),
  ('Aisha', 22, 'Kaduna', 89);

This is much faster than running separate INSERT statements for each row.

INSERT from SELECT

You can insert data based on a query. This is useful for copying data between tables:

-- Create a backup table and copy data into it
CREATE TABLE students_backup (
  id INTEGER, name TEXT, age INTEGER, city TEXT, score INTEGER
);

INSERT INTO students_backup
SELECT * FROM students WHERE score > 80;

SELECT * FROM students_backup;

This is one of those features that seems simple but is incredibly powerful in real-world scenarios — archiving, reporting, data migration, all use this pattern.