INSERT .. SELECT

You can use the results of a SELECT statement to insert rows into another table.

INSERT INTO games(yr,city)
  SELECT yr+12, city FROM games;

The table games shows the year and the city hosting the Olympic Games.

games (before) yr city 2000 Sydney 2004 Athens 2008 Beijing

games (after) yr city 2000 Sydney 2004 Athens 2008 Beijing 2012 Sydney 2016 Athens 2020 Beijing

schema:scott

 DROP TABLE games

 CREATE TABLE games(
  yr INTEGER,
  city VARCHAR(20));
INSERT INTO games VALUES (2000,'Sydney');
INSERT INTO games VALUES (2004,'Athens');
INSERT INTO games VALUES (2008,'Beijing');

The INSERT SELECT statement adds a new row to the table based on a SELECT statement: In this example you run the next three Olympic games in the same three venues:

INSERT INTO games(yr,city)
  SELECT yr+12, city FROM games;
SELECT * FROM games;

INSERT INTO games(yr,city)
  SELECT yr+12, city FROM games;

SELECT * FROM games;

See also

• INSERT .. VALUES