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;

Notes:

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

-- InsertRows.sql
-- Copyright (c) 2015, 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.

Last update: 2015.

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 Perl Programs and MySQL Servers

 PHP 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

 DELETE FROM - Statement to Delete Records from Tables

 Using SELECT to Query Database

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 Outdated Tutorials

 References

 PDF Printing Version