Doing More with MySQL - Learning MySQL - page 280

The LIMIT 10 is there to limit the number of rows returned by the SELECT ; we've limited
in this example simply for readability, but in practice you'd limit it to 999 because that's
the maximum sequence_id you want to use.
The SELECT statement in an INSERT INTO statement can use all of the features of
SELECT statements. You can use joins, aggregation, functions, and any other features
you choose. You can also query data from one database into another, by prefacing the
table names with the database name followed by a period ( . ) character. For example,
if you wanted to insert the artist table from the music database into a new art database,
you could do the following:
mysql> CREATE DATABASE art;
Query OK, 1 row affected (0.01 sec)
mysql> USE art;
Database changed
mysql> CREATE TABLE people (
-> people_id SMALLINT(4) NOT NULL,
-> name CHAR(128) NOT NULL,
-> PRIMARY KEY (people_id));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO art.people (people_id, name)
-> SELECT artist_id, artist_name FROM music.artist;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
You can see that the new people table is referred to as art.people (though it doesn't
need to be, since art is the database that's currently in use), and the artist table is
referred to as music.artist (which it needs to be, since it isn't the database being used).
Note also that the column names don't need to be the same for the SELECT and the
INSERT .
Sometimes, you'll encounter duplication issues when inserting with a SELECT statement.
This occurs if you try to insert the same primary key value twice; it won't happen in
the shuffle table, as long as you automatically allocate a new sequence_id using the
auto_increment feature. However, when you try to insert duplicate key values, MySQL
will abort. Let's force a duplicate into the shuffle table to show the behavior:
mysql> USE music;
Database changed
mysql> INSERT INTO shuffle (artist_id, album_id, track_id, sequence_id)
-> SELECT artist_id, album_id, track_id, 1 FROM track LIMIT 1;
ERROR 1062 (23000): Duplicate entry '1' for key 1
If you want MySQL to ignore this and keep going, add an IGNORE keyword after the
INSERT :
mysql> INSERT IGNORE INTO shuffle (artist_id, album_id, track_id, sequence_id)
-> SELECT artist_id, album_id, track_id, 1 FROM track LIMIT 1;