Using the INSERT Command

Using the INSERT Command

After you have created some tables, you'll use the SQL command INSERT for adding new records to these tables. The basic syntax of INSERT is

 INSERT INTO table_name (column list) VALUES (column values); 

Within the parenthetical list of values, you must enclose strings within quotation marks. The SQL standard is single quotes, but MySQL enables the usage of either single or double quotes. Remember to escape the type of quotation mark used, if it's within the string itself.

Integers do not require quotation marks around them.

Here is an example of a string where escaping is necessary:

 O'Connor said "Boo" 

If you enclose your strings in double quotes, the INSERT statement would look like this:

 INSERT INTO table_name (column_name) VALUES ("O'Connor said \"Boo\""); 

If you enclose your strings in single quotes instead, the INSERT statement would look like this:

 INSERT INTO table_name (column_name) VALUES ('O\'Connor said "Boo"'); 

A Closer Look at INSERT

Besides the table name, there are two main parts of the INSERT statement the column list and the value list. Only the value list is actually required, but if you omit the column list, you must specifically name each column in your values list in order.

Using the grocery_inventory table as an example, you have five fields: id, item_name, item_desc, item_price, and curr_qty. To insert a complete record, you could use either of these statements:

1. A statement with all columns named:

    insert into grocery_inventory (id, item_name, item_desc, item_price, curr_qty) values ('1', 'Apples', 'Beautiful, ripe apples.', '0.25', 1000); 

2. A statement that uses all columns but does not explicitly name them:

    insert into grocery_inventory values ('2', 'Bunches of Grapes', 'Seedless grapes.', '2.99', 500); 

Give both of them a try and see what happens. You should get results like this:

 mysql> insert into grocery_inventory     -> (id, item_name, item_desc, item_price, curr_qty)     -> values     -> (1, 'Apples', 'Medium-sized Granny Smith apples.', 0.25, 1000); Query OK, 1 row affected (0.01 sec) mysql> insert into grocery_inventory values (2, 'Bunches of Grapes',     -> 'Seedless grapes.', 2.99, 500); Query OK, 1 row affected (0.01 sec) 

Now for some more interesting methods of using INSERT. Because id is an auto-incrementing integer, you don't have to put it in your values list. However, if there's a value you specifically don't want to list (such as id), you then must list the remaining columns in use. For example, the following statement does not list the columns and also does not give a value for id, and it will produce an error:

 mysql> insert into grocery_inventory values     -> ('Bottled Water (6-pack)', '500ml spring water.', 2.29, 250); ERROR 1136: Column count doesn't match value count at row 1 

Because you didn't list any columns, MySQL expects all of them to be in the value list, causing an error on the previous statement. If the goal was to let MySQL do the work for you by auto-incrementing the id field, you could use either of these statements:

1. A statement with all columns named except id:

    insert into grocery_inventory (item_name, item_desc, item_price, curr_qty) values ('Bottled Water (6-pack)', '500ml spring water.', '2.29', 250); 

2. A statement that uses all columns, but does not explicitly name them and indicates a NULL entry for id (so one is filled in for you):

    insert into grocery_inventory values ('NULL', 'Bottled Water (6-pack)', '500ml spring water.', 2.29, 250); 

Go ahead and pick one to use so that your grocery_inventory table has three records in total. It makes no different to MySQL, but as with everything that is a preference, be consistent in your application development. Consistent structures will be easier for you to debug later because you'll know what to expect.