The INSERT Clause With a Column List
- You can INSERT single or multiple rows at a time.
- An INSERT with a column list DOES NOT require you to provide a value for each column. If you do not want to provide a value for a specific column, you do not have to include it in the column list. For columns that allow null values, the system will automatically provide a null value for you.
- If you want a column that provides a default value such as an auto-increment column to be populated with the default value, you do not need to list the column in the column list. The system will automatically provide the default value.
- When coding with a column list, the columns may appear in any order as long as the VALUES list matches the order of the column list.
Below is a basic example of an INSERT statement with a column list:
1 USE world;
2 INSERT INTO city
3 (name, countryCode, district, population)
5 ("San Felipe", "CHL", "Valparaiso", 64126);
Results of the Insert:
INSERT INTO city
- Insert the value into the city table. The INTO keyword is not required.
(name, countryCode, district, population)
- The column list is comma-separated and enclosed in parentheses.
- The VALUES keyword is between the column list and the actual values. No commas are necessary.
("San Felipe", "CHL", "Valparaiso", 64126);
- The values order must appear in the corresponding order of the column list.
- You must enclose strings in quotes.
- You must not enclose numbers in quotes.
- You do not have to specify columns that allow null values or default values in the column list. They will automatically get a null or default value.