When you need to add data into your database, use the SQL INSERT INTO command. With SQL INSERT, you can specify the table you want to insert data into, the columns you want to insert data into, and the values you want to insert.
Examples of SQL Insert Into
Below are multiple examples of the SQL INSERT command to add data into your SQL tables. We'll be using the Sakila actor and category tables that were downloaded in a previous lesson. If you haven't already, follow the steps to install the Sakila DB, then head back here.
1. Insert two values in two columns
Add a new actor to the actor's table:
INSERT INTO sakila.actor (first_name, last_name)
VALUES ("Coding", "Nomads");
In the query above, you can see that you are INSERT(ing) INTO the sakila.actor table. The columns you're inserting into are first_name and last_name. The values you want to be inserted into those fields are "Coding" and "Nomads".
Auto-generated fields
Auto-generated fields such as actor_id and last_update are auto-generated by MySQL. So you do not need to insert them manually. MySQL will auto-generate them. You can verify this by looking at the table structure:
See how the actor_id field has the AI box ticked? That AI means auto-increment. This means it will automatically increment the actor_id (based on the last record) and insert it into all new records.
Timestamps
Also, note how the "last_update" field has a "Default / Expression" assigned to CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. This means that all new records will have the current timestamp automatically inserted upon insert, as well as ON UPDATE (when the record is updated at any time, it will also update this timestamp).
2. Insert one value into one column
Here's another INSERT statement, this time for the categories table. In this case, the categories table only has 3 fields. Two of which are autogenerated. So, all you have to do is INSERT the one non-auto-generated field.
INSERT INTO sakila.category
(name)
VALUES
("Cartoon"); --value to insert into it
And if you run:
select * from category where name = "Cartoon";
You can see that the record was indeed inserted.
Summary: SQL Insert Into Command
The SQL INSERT command is used to insert new data into a database table.
SQL Insert Into Syntax
Here's the syntax for the SQL INSERT command, where you can substitute the variables in < > with your values.
INSERT INTO <YOUR_TABLE>
(<YOUR_COLUMNS>)
VALUES
(<YOUR_VALUES>);
Now that you can add data into the table row by row, how about creating a table using the results of a SELECT statement? Check out the next article to find out how.