INSERT INTO - Statement to Insert Records to Tables

MySQL Tutorials - Herong's Tutorial Examples

∟Using DML to Insert, Update and Delete Records

∟INSERT INTO - Statement to Insert Records to Tables

A tutorial example is provided on how to use INSERT INTO statements to insert records into tables in different ways.

An insert statement allows you to insert new rows of data into an existing table. It has a number of syntax formats:

1. To insert a single row of all columns with values resulting from the specified expressions:

INSERT INTO tbl_name VALUES (expression, expression, ...);

When executed, all expressions will be evaluated, and the resulting values will form the new row, which will be inserted into the specified table. Of course, the number of expressions must be equal to the number of columns in table.

2. To insert a single row with some columns having specified values, and others having default values:

INSERT INTO tbl_name (column, column, ...) VALUES (expression,
   expression, ...);

Notes:

Obviously, duplicated columns are not allowed in the column list.
The number of expressions must be equal to the number of specified columns.
Default values will be provided for those columns that are not specified in the column list.

3. To insert one or more rows of all columns with a select sub-statement:

INSERT INTO tbl_name select_statement;

When executed, the output rows of the select sub-statement will be inserted into the specified table. Of course, the number of select expressions in the select statement must be equal to the number of columns of the specified table.

4. To insert one or more rows with some column having values from the specified select sub-statement, and other columns having default values:

INSERT INTO tbl_name (column, column, ...) select_statement;

5. To insert multiple rows with multiple VALUES clauses:

INSERT INTO tbl_name (c_list) VALUES (e_list), (e_list), ..., (e_list);

Notes:

Obviously, duplicated columns are not allowed in the column list.
The number of select expressions must be equal to the number of specified columns.
Default values will be provided for those columns that are not specified in the column list.

Here is an example SQL code, InsertRows.sql, showing you how to insert rows into an existing table:

-- InsertRows.sql
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.
--
DROP TABLE IF EXISTS User;
CREATE TABLE User (Login VARCHAR(8), Password CHAR(8));
INSERT INTO User VALUES ('herong','8IS3KOXW');
INSERT INTO User (Login) VALUES ('mike');
INSERT INTO User SELECT Login, Password FROM User;
INSERT INTO User (Password) SELECT CONCAT('__',Login) FROM User;
SELECT 'User table:' AS '---';
SELECT * FROM User;

If you run the code, you will get:

---
User table:
Login   Password
herong  8IS3KOXW
mike    NULL
herong  8IS3KOXW
mike    NULL
NULL    __herong
NULL    __mike
NULL    __herong
NULL    __mike