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:

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:

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

The output looks alright to me.

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 PHP Programs and MySQL Server

 Perl Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

 Using DDL to Create Tables and Indexes

Using DML to Insert, Update and Delete Records

INSERT INTO - Statement to Insert Records to Tables

 UPDATE - Statement to Update Records in Tables

 UPDATE with Joined Tables

 DELETE FROM - Statement to Delete Records from Tables

 Using SELECT to Query Database

 Window Functions for Statistical Analysis

 Use Index for Better Performance

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 System, User-Defined and Stored Procedure Variables

 MySQL Server Administration

 Storage Engines in MySQL Server

 InnoDB Storage Engine - Primary and Secondary Indexes

 Performance Tuning and Optimization

 Bulk Changes on Large Tables

 MySQL Server on macOS

 Installing MySQL Server on Linux

 Connection, Performance and Second Instance on Linux

 Archived Tutorials

 References

 Full Version in PDF/EPUB