General Format for SQL INSERT Statement

The general format of the INSERT statement is:

INSERT INTO table_name (column list) 
values(value list)

The keyword INTO is optional, but it can help in the readability of your INSERT statement when you review it later. It will not hurt anything to have it in the statement, but the statement has no meaning to the SQL database engine. As you might have guessed, table_name refers to the table you want to insert the information into. In our ongoing example from previous lessons, the table is named Customer. The column list is where you indicate which columns you are providing information for. Keep in mind that columns that you do not provide information for will default to blank, or null values. If your database does not allow nulls for a given column that you have left blank, you will get an error message indicating that you are not allowed to insert blanks for that column value. This is an example of an INSERT statement:

INSERT INTO MyTable (Lastname, Firstname)
  VALUES ("Gauss", "Karl")
INSERT INTO MyTable (Lastname, Firstname) 
  VALUES ("Newton", "Isaac")
INSERT INTO MyTable (Lastname, Firstname)
  VALUES ("Hilbert", "David")

Here is a tip if you plan to use automatically generated values. With many database engines, you have the ability to create a column that will be automatically created whenever information is saved to the column. For example, with Microsoft SQL Server (as well as Sybase ), you can create a column that is referred to as an identity column. When information is stored in this type of column, the database engine automatically assigns the next available value to the column. One example of where this is commonly used is the automatic creation of an ID such as the customer ID that you created in the sample table. By creating the table column as an identity type, you can let the server number the records for you. You will need to know what columns in your tables are established this way because, when you do an insert, you must not provide values for those columns. Because the system generates them automatically, you will receive an error message if you insert such values explicitly in the database table. Simply omit them in your INSERT statement and you will be all set.

MySQL treats indexes as optional components of a table, which is why you must use the alter table command to add or remove an index. Other database servers, including SQL Server and Oracle Database, treat indexes as independent schema objects. For both SQL Server and Oracle, therefore, you would generate an index using the create index command, as in:

CREATE INDEX dept_name_idx
ON department (name);

As of MySQL version 5.0, a create index command is available, although it is mapped to the alter table command. All database servers allow you to look at the available indexes. MySQL users can use the show command to see all of the indexes on a specific table, as in:

mysql> SHOW INDEX FROM department \G *************************** 1. row
*************************** 1. row ***************************
Table: department
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: dept_id
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_Comment:
*************************** 2. row ***************************
Table: department
Non_unique: 1
Key_name: dept_name_idx
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_Comment:
2 rows in set (0.01 sec)

The output shows that there are two indexes on the department table: one on the dept_id column called PRIMARY , and the other on the name column called dept_name_idx . Since I have created only one index so far (dept_name_idx ), you might be wondering where the other came from; when the department table was created, the create table statement included a constraint naming the dept_id column as the primary key for the table. Here's the statement used to create the table:

CREATE TABLE department
(dept_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
CONSTRAINT pk_department PRIMARY KEY (dept_id) );

When the table was created, the MySQL server automatically generated an index on the primary key column, which, in this case, is dept_id , and gave the index the name PRIMARY . If, after creating an index, you decide that the index is not proving useful, you can remove it by means of the following:

mysql> ALTER TABLE department
-> DROP INDEX dept_name_idx;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

Note: SQL Server and Oracle Database users must use the drop index command to remove an index, as in:

DROP INDEX dept_name_idx; (Oracle)
DROP INDEX dept_name_idx ON department (SQL Server)
MySQL now also supports a drop index command.

The following example inserts values into the SmartPersons table's LastName and FirstName fields. It gets the values from a query that selects FirstName and LastName values from the Persons table where the corresponding record's State value is not Confusion:

INSERT INTO SmartPersons (LastName, FirstName)
SELECT LastName, FirstName FROM Persons
WHERE State <> 'Confusion'

Unlike the previous INSERT statements, this version may insert many records in the table if the query returns a lot of data. We will continue to examine this statement in the next lesson.