Now that we've created our tables, let's add some data.

The INSERT Statement

The INSERT statement allows you to add data to your database tables. The syntax goes like this:

INSERT INTO table_name (col_1, col_2, col_3) 

VALUES (value_1, value_2, value_3);

This inserts data into one row. The order of the values provided must correspond with the columns that the values are to be inserted into.

If you are inserting data into all columns, you can omit the column names and just do this:

INSERT INTO table_name VALUES (value_1, value_2, value_3);

To populate multiple rows, use a comma to separate each row, like this:

INSERT INTO table_name 

VALUES 

(value_1, value_2, value_3),

(value_1, value_2, value_3),

(value_1, value_2, value_3),

(value_1, value_2, value_3);

The above example populates 4 rows and assumes there are 3 columns in each row.

Example

The following script can be used to dump data into our FruitShop database. We use two INSERT statements — one for each table we want to populate. The first populates the Units table, the second populates the Fruit table.

Execute the following SQL script against the FruitShop database:

INSERT INTO Units 

VALUES 

(1,'Piece','2015-02-15 10:30:00','2015-02-15 10:30:00'),

(2,'Kilogram','2015-02-15 10:30:00','2015-02-15 10:30:00'),

(3,'Gram','2015-02-15 10:30:00','2015-02-15 10:30:00'),

(4,'Pound','2015-02-15 10:30:00','2015-02-15 10:30:00'),

(5,'Ounce','2015-02-15 10:30:00','2015-02-15 10:30:00'),

(6,'Bunch','2015-02-15 10:30:00','2015-02-15 10:30:00'),

(7,'Container','2015-02-15 10:30:00','2015-02-15 10:30:00');

INSERT INTO Fruit 

VALUES 

(1,'Apple',10,1,'2015-02-15 10:30:00','2015-02-15 10:30:00'),

(2,'Orange',5,2,'2015-02-15 10:30:00','2015-02-15 10:30:00'),

(3,'Banana',20,6,'2015-02-15 10:30:00','2015-02-15 10:30:00'),

(4,'Watermelon',10,1,'2015-02-15 10:30:00','2015-02-15 10:30:00'),

(5,'Grapes',15,6,'2015-02-15 10:30:00','2015-02-15 10:30:00'),

(6,'Strawberry',12,7,'2015-02-15 10:30:00','2015-02-15 10:30:00');

Check your Data

You can check that your data was inserted by executing the following SQL statements.

1. Check the Fruit Table

   Select all records from the Fruit table by running the following statement:

    

   select * from Fruit

   The Result

   The result should look like this.

2. Check the Units Table

   Select all records from the Units table table by running the following statement:

    

   select * from Units

   The Result

   The result should look like this.