After creating a MySQL Table with CREATE TABLE statement, you can proceed to add data in the table. MySQL INSERT statement is used to do this task. MySQL Insert statement is one of the Data Manipulation Language statements defined in Structured Query Language (SQL). You can insert one or more records with one Insert Statement. One Insert statement may be used to insert data in all columns or a few selected columns. Let’s see how it works
Syntax – MySQL INSERT Statement
INSERT INTO [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] table-name [(column-name1, column-name2,….)] VALUES (value-column11, value-column12,…), (value-column21, value-column22,…), …..
- INSERT, INTO and VALUES – are the keywords for inserting row(s) in a table
- LOW_PRIORITY- is used for low priority insert statement. It means insertion can be delayed if other reading operations are being done on the table.
- HIGH_PRIORITY- used for high priority insert operations. It overrides existing low-priority server setting.
- IGNORE- this option can be used for ignorable errors while insertion operation.
- table-name is the name of an already created table
- (column-name1, column-name2 …) are the names of the columns for which data is to be inserted. Giving name of columns is optional if you want to fill data against all the columns of the table.
- (value-column11, value-column12,…), (value-column21, value-column22,…), …..are the sets of values that you give in the INSERT statement for each column defined in column list. Enclosing the values in brackets will make that set as one record. This way you can insert multiple records with one insert statement.
Rules – using MySQL Insert Statement
- The count of columns and values should match. All the values must also match corresponding columns’ data types defined while creating the table. If you forget the structure of the table you can use statement SHOW COLUMNS FROM table-name statement.
- Data types of the values must match the data types of the corresponding columns. Number values must be given without quotes. Text values have be enclosed in single quotes. For example city name ‘London’ is enclosed in quotes. Date format is ‘yyyy-mm-dd’
Examples – INSERT statement
One Row- All Columns
To insert values in all columns of a table you need not specify the affected column name of the table before VALUES keyword. Provide values for all the columns in the sequence they appear in the table definition.
INSERT INTO artists VALUES (2,3,'David Kelly','UK','1987-09-09',34,'Mango','2001-02-02',1);
Multiple Rows – All Columns
To insert multiple rows covering all columns of a table provide multiple sets of values for all columns in their sequence of existence in the table definition. Each such set of values must be enclosed in parenthesis. All such sets are separated by commas.
INSERT INTO artists VALUES (3,1,'Minja','UK','1987-01-01',32,'Humma','2001-02-02',1), (3,2,'Marina Block','UK','1987-01-01',32,'Bring it on','2001-02-02',1), (3,4,'Rocky Glone','Germany','1987-01-01',32,'Bright Days','2001-02-02',1);
One Row-Selected Columns
You can insert values in a selected set of columns by giving the names of the columns after the table name and before VALUES keyword. You can skip some columns and give values for only those columns in which you want to insert values. Column names may not follow the sequence that of columns in table definition. Ensure that you include the primary key and not null column names and their values.
INSERT INTO artists (artist_id, artist_name) VALUES (5,'David Bottom');
You will observe that the columns that are not listed in INSERT query are updated with null.
Multiple Rows-Selected Columns
You can insert set of values in a selected set of columns by specifying the names of the columns in MySQL Insert query. These sets must be enclosed in parenthesis and separated by commas.
INSERT INTO artists (artist_id, artist_name) VALUES (7,'Regine Jonas'),(6,'Helen Blunt'),(8,'Bloby Lyon');
Points to remember
To add Data in Table with MySQL Insert statement-
- If the table uses Foreign Key Constraint, choose values available in the corresponding Primary Key Column of the referenced table.
- To insert data in only a few columns make sure that you do include the Primary Key Column and its value.
Read More about MySQL Insert Statement here.