Inserting Data into a MySQL Database
A database would be of little use if it did not contain any data. It is, therefore, not surprising that the INSERT statement is one of the most commonly used statements.
In this chapter of MySQL Essentials we will explain how to insert data into database tables using the SQL INSERT statement.
The Basics of Data Insertion
The purpose of the SQL INSERT statement is to add new rows of data to a specified database table. Using the INSERT statement, it is possible to insert a complete row, a partial rwo, multiple rows or rows generated as the result of a database query. In this chapter we will look at each of these techniques in turn.
Inserting a Complete Row
In order to add a single, complete row to a table the INSERT statement must be provided with the name of the table which the row is to be added, together with the column names and associated values to be added. The INSERT command first takes a comma separated list of column names enclosed in parentheses. This is followed by the VALUES keyword and then a comman separated list of values for each column. Note that the values must be listed in the same order as the column names. For example:
INSERT INTO products( product_id, product_name, product_description, product_location, product_quantity) VALUES( NULL, 'CD-RW Model 4543', 'CD Writer', 'Shelf 4B', 10 };
Note that the product_id is specified as NULL. This is because the product_id in our imaginmary table is set to AUTO_INCREMENT so we do not specifically set this. Instead, the MySQL database engine will automatically create a new value for us.
A less safe way to add records to a table is to provide only the values. Whilst this approach works it is vital that the values be specified in the exact order in which the columns were specified when the table was created. Getting the wrong order will, at the very least result in a an error message (if the data types do not match the columns) and at the worst result in data going into the wrong columns. The other danger of this approach is that the statement may work based on the current table layout but will cause porblems should the layout be altered at a later date. With these warnings in mind, here is an example of specifying just the values:
a comma separated list of column names enclosed in parentheses. This is followed by the VALUES keyword and then a comman separated list of values for each column. Note that the values must be listed in the same order as the column names. For example:
INSERT INTO products VALUES( NULL, 'CD-RW Model 4543', 'CD Writer', 'Shelf 4B', 10 };
Adding Multiple Rows to a Table
Adding multiple rows to a table can be achieved either using multiple INSERT statements or by submitting all the rows as part of a single INSERT statement. When adding multiple rows to a MySQL database table using a single INSERT statement, simply separate each set of data with a comma:
INSERT INTO products( product_id, product_name, product_description, product_location, product_quantity) VALUES( NULL, 'CD-RW Model 4543', 'CD Writer', 'Shelf 4B', 10 }, ( NULL, 'Disk Drive 4500', '60Gb 4500rpm Disk Drive', 'Shelf 6A', 100 ), ( NULL, 'Kingston Ci20', 'Wired Optical Mouse', 'Shelf 6F', 24 );
Inserting Results from a SELECT Statement
A particularly useful SQL statement involves combining the INPUT and SELECT statements. In this scenario, the SELECT statement reads the values (typically from anotehr table) to be added to the current table. This approach is known as INSERT SELECT. and can be used to perform tasks such as added all the rows from one table to another table with the same column layout. The following example uses a SELECT statement to retrieve the rows in a table called old_products and adds them to a table called new_products:
INSERT INTO new_products ( product_id, product_name, product_description, product_location, product_quantity ) SELECT product_id, product_name, product_description, product_location, product_quantity FROM old_products;
Reducing the INSERT Performance Load
The INSERT satatment places a relatively high load on the database server. In fact a high volume of insertions can have an adverse effect on other transactions (such as reading data). To reduce the load imposed by a data insertion consider using the LOW_PRIORITY keyword. For example:
INSERT LOW_PRIORITY INTO products( product_id, product_name, product_description, product_location, product_quantity) VALUES( NULL, 'CD-RW Model 4543', 'CD Writer', 'Shelf 4B', 10 };
Reducing the INSERT Performance Load
The INSERT satatment places a relatively high load on the database server. In fact a high volume of insertions can have an adverse effect on other transactions (such as reading data). To reduce the load imposed by a data insertion consider using the LOW_PRIORITY keyword. For example:
INSERT LOW_PRIORITY INTO products( product_id, product_name, product_description, product_location, product_quantity) VALUES( NULL, 'CD-RW Model 4543', 'CD Writer', 'Shelf 4B', 10 };