Critical Skill 2.4 Insert Data into Tables

Placing data into a previously created table is accomplished with the INSERT command. The INSERT command performs this task one record or table row at a time. The inserted fields can be partially or completely specified. In the case of partially specified fields, MySQL inserts the default value into the unspecified field.

There are three basic variations on the INSERT syntax: INSERT . . . VALUES and INSERT ...SET , which inserts specific values provided in the command code, and INSERT . . . SELECT , which inserts values provided in other tables:

INSERT INTO <table_name> [<column_name1[, <column_name2, . . .]VALUES <column_value1>[, <column_value2>, . . .];

The INSERT . . . VALUES syntax requires a table_name value and it gives you the option of specifying a list of column names . The column_name values need to be listed in the order in which they appear in the actual table description. In addition, the list of column_name s and list of column_value s must match, respectively, or the data willbe inserted into the wrong column. Because this version of the syntax inserts data in all the columns , you will find it is easier and less work to state only the table_name , letting MySQL attend to the column_name data. You can then focus on making sure the list of column_value s is correctly ordered. If data is missing for a particular field, then use the NULL value, which will cause either the DEFAULT value to be inserted in that field or a marker value (such as for a numeric field or "n/a" for a string field) to fill the place of the missing data:

INSERT INTO < table_name >SET < column_name > = < column_value > [, < column_name > = < column_value > , . . .];

The INSERT ...SET syntax allows you to put values in specified columns within a record, and the < column_name > = < column_value > format makes it easier to assure the correct match between column and data, especially if the record in question has a lot of fields. Any column not specified in the command will have the DEFAULT value inserted into it.

INSERT INTO < table_name > [ < column_name1[, < column_name2, . . .] SELECT < select_value > FROM < table_name > ;

The INSERT... SELECT syntax allows you to use a SELECT command to read another table or tables and insert its data into the specified table_name . The select_value canbe a wildcard ( * ), an expression (i.e. column_value+3 ), or a column_name or list of column_name s. The column_name s in each table description must be in respective order, and once again, any column not specified has the DEFAULT value placed into it.

As of MySQL version 4.1.0, all three of these INSERT syntaxes will allow the addition of the ON DUPLICATE KEY UPDATE clause at the end of the command but before the semicolon. This clause allows you to specify what will happen if the record you try to insert duplicates a key that has been designated as UNIQUE or PRIMARY by allowing you to update the specified field or fields of the unique record rather than insert a duplicate record. The following syntax shows this clause attached to the INSERT . . .VALUES syntax, but it can be used in the same position in all of the INSERT commands:

INSERT INTO < table_name > [< column_name1 [ , < column_name2, . . .]VALUES < column_value1 >[ , < column_value2 >, . . .][ON DUPLICATE KEY UPDATE < column_name1 > = < expression1 >[, < column_name2 > = < expression2 > , . . .];