MariaDB Crash Course - Ben Forta [58]
* * *
Using this syntax, you can also omit columns. This means you provide values only for some columns, but not for others. (You’ve actually already seen an example of this, cust_id was omitted when column names were explicitly listed.)
* * *
Caution: Omitting Columns
You may omit columns from an INSERT operation if the table definition so allows. One of the following conditions must exist:
• The column is defined as allowing NULL values (no value at all).
• A default value is specified in the table definition. This means the default value will be used if no value is specified.
If you omit a value from a table that does not allow NULL values and does not have a default, MariaDB generates an error message, and the row is not inserted.
* * *
* * *
Tip: Improving Overall Performance
Databases are frequently accessed by multiple clients, and it is MariaDB’s job to manage which requests are processed and in which order. INSERT operations can be time consuming (especially if there are many indexes to be updated), and this can hurt the performance of SELECT statements waiting to be processed.
If data retrieval is of utmost importance (as it usually is), you can instruct MariaDB to lower the priority of your INSERT statement by adding the keyword LOW_PRIORITY in between INSERT and INTO, like this:
INSERT LOW_PRIORITY INTO
Incidentally, this also applies to the UPDATE and DELETE statements that you learn about in the next chapter.
* * *
Inserting Multiple Rows
INSERT inserts a single row into a table. But what if you need to insert multiple rows? You could simply use multiple INSERT statements, and could even submit them all at once, each terminated by a semicolon, like this:
Input
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA');
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('M. Martian',
'42 Galaxy Way',
'New York',
'NY',
'11213',
'USA');
Or, as long as the column names (and order) are identical in each INSERT, you could combine the statements as follows:
Input
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES(
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA'
),
(
'M. Martian',
'42 Galaxy Way',
'New York',
'NY',
'11213',
'USA'
);
Analysis
Here a single INSERT statement has multiple sets of values, each enclosed within parentheses and separated by commas.
* * *
Tip: Improving INSERT Performance
This technique can improve the performance of your database processing, as MariaDB processes multiple insertions in a single INSERT faster than it processes multiple INSERT statements.
* * *
Inserting Retrieved Data
INSERT is usually used to add a row to a table using specified values. Another form of INSERT can be used to insert the result of a SELECT statement into a table. This is known as INSERT SELECT, and, as its name suggests, it is made up of an INSERT statement and a SELECT statement.
Suppose you want to merge a list of customers from another table into your customers table. Instead of reading one row at a time and inserting it with INSERT, you can do the following:
* * *
Note: Instructions Needed for the Next Example
The following example imports data from a table named custnew into the customers table. To try this example, create a new table named custnew using the CREATE TABLE customers statement in create.sql, and obviously replacing customers with custnew. Then add a few customers of your own, being careful to not use cust_id values that were already used in customers (the subsequent INSERT operation will fail if primary