Insert From Select

You can also insert new rows into a table from another table using insert from SELECT statement. The syntax for the insert from SELECT statement is given below. The SELECT statement was described in detail in the Retrieving Data from a Database section and its use with the INSERT statement will show the basics of how the two can be used together.

insert_stmt:
          INSERT INTO [database_name.]table_name [( column_name [, column_name]... )]
               FROM select_stmt

The number of result columns returned from the select_stmt must equal the number of columns specified in the column_name list or, if not specified, the number of columns declared in the table. The data type of each result column must also be compatible with its corresponding table column.

The following example uses the weather sensor database example discussed in the Defining a Database section. The SELECT statement retrieves data from the various weather sensors and stores the results in the weather_summary table. It uses the limit clause to specify that the data is to be accumulated and summarized every 60 minutes. Even though only the SQL statements are shown, the execution of the statement would be performed inside a loop in the application program. One row per longitude and latitude, date, and hour of the day is stored in the weather_summary table. Note that the execution time for this statement is one hour.

insert into weather_summary from 
     select loc_long, loc_lat, curdate(), hour(rdg_time)
          avg(temperature), avg(pressure), avg(humidity), avg(light)
          from weather_data
          group by 1, 2, 3, 4 limit(60 mins);