Creating a New Table from an Existing One with SELECT INTO

The SELECT INTO statement creates a new table and populates it with the result of a SELECT. It’s similar to creating an empty table with CREATE TABLE and then populating the table with INSERT SELECT (see “Inserting Rows with INSERT” in Chapter 9). But note that SELECT INTO exports rows from an existing table, whereas INSERT SELECT imports rows into an existing table. SELECT INTO commonly is used to:

  • Archive specific rows

  • Make backup copies of tables

  • Create a snapshot of a table at a particular moment in time

  • Quickly duplicate a table’s structure but not its data

  • Create test data

  • Copy a table to test INSERT, UPDATE, and DELETE operations before modifying production data.

When you’re using ...

Get SQL: Visual QuickStart Guide now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.