SQL, adding data to a table

Once you have a table, you can insert data into it.

Take this table:

CREATE TABLE people (
  age INT,
  name CHAR(20)
);

You can now start adding data into it with the INSERT INTO command:

INSERT INTO people VALUES (37, 'Flavio');

This form relies on the order of the columns in the table definition. The first value goes into age, the second into name, because that is the order they were declared in.

That works, but it’s fragile. If someone later adds a column or reorders the table, the same statement inserts values into the wrong places, or fails.

My advice is to always name the columns you are inserting into:

INSERT INTO people (age, name) VALUES (37, 'Flavio');

Now the statement says exactly which value goes where. It keeps working even if the table gains new columns, and anyone reading it understands it without looking up the schema.

Naming columns also lets you skip some. Any column you leave out gets its default value, or NULL if it has no default:

INSERT INTO people (name) VALUES ('Syd');

Here age is NULL for Syd, because we didn’t provide it.

Inserting multiple rows

You can insert multiple items separating each one with a comma:

INSERT INTO people VALUES (37, 'Flavio'), (8, 'Roger');

One statement with many rows is much faster than many statements with one row each, because the database does the parsing and the transaction work once. When you import hundreds of rows, batch them.

Verify the insert worked

The database confirms each insert with a count. PostgreSQL replies INSERT 0 1 for one row, INSERT 0 2 for two. MySQL says Query OK, 1 row affected.

To see the actual data, run a SELECT:

SELECT * FROM people;

 age |  name
-----+--------
  37 | Flavio
   8 | Roger

One common failure: inserting a value with the wrong type, like a string into age. The database rejects it with an error such as invalid input syntax for type integer. Read the error, fix the value, and run the statement again. Nothing was written, so there is nothing to clean up.

Related posts about database: