# INSERT INTO: add data to a table
Run through and play with the interactive database for this lecture to learn about this better!
The SQL command for adding data to a table is called
INSERT INTO. This tells us that we must provide the table name into which we want to insert data, and naturally we'll also have to provide what data we want to insert.
However before inserting data through SQL, let's do it through DB Browser.
# Inserting data with DB Browser
In DB Browser, open the "Browse Data" tab.
Then, select the table into which you want to insert data.
On the right hand side, click on "New Record".
Then you can click on the new cells and edit their contents. I'll be adding a user called "John Smith" here.
Remember to save the database when making changes, as otherwise you may lose the contents if your computer turns off unexpectedly.
To save the database click on "Write Changes" at the top.
This requirement is common in databases, because databases normally work fully outside of the hard drive, without saving anything, until specifically told to do so. That helps improve performance as hard drives are quite slow to write to.
# Inserting data with SQL
Now let's try to insert another user but only with SQL.
First, navigate to the "Execute SQL" tab.
There, we'll be writing our query:
INSERT INTO users VALUES ('Rolf', 'Smith');
This query also has three parts:
INSERT INTO users: this tells the database we want to insert data, and into which table we want to insert data.
VALUES ('Rolf', 'Smith'): this tells the database which values we want to insert. The keyword
VALUESis followed by the values themselves within brackets. It's important to use single quotes here for
- The semicolon at the end:
It's very important to use single quotes for the person's name. Text contents of cells often use single quotes. Double quotes are reserved for something else, and not often used.
We'll be using double quotes later on in the course.
# Why is
A common question is "why does SQL require the
VALUES keyword"? Wouldn't it be easier (and more natural) to do this?
INSERT INTO users ('Rolf', 'Smith');
It would certainly be shorter and simpler, but there is something we can add between the table name and
VALUES, which doesn't make sense if
VALUES isn't there.
Here's another query used to create a new row in the table, but without providing a surname for the user:
INSERT INTO users (first_name) VALUES ('Rolf');
Between the table name and
VALUES we can, inside brackets, provide the column names into which we want to add data.
In fact, it's often recommended to use this feature even when inserting into all columns of a table. That way your query is tied to the table structure. If your table structure changes (e.g. you delete the table and re-create it with different columns) but you forget to change your query, data will still be going into the right columns.
INSERT INTO users (first_name, surname) VALUES ('Rolf', 'Smith');