The SQL INSERT statement is most commonly used to insert individual rows into a table.

But you can also insert the results of a query into a table. This means that you can insert multiple rows at once (as long as they’re returned by the query).

Basic Example

Here’s a basic example to demonstrate.

INSERT INTO Pets2
SELECT * FROM Pets;

That inserts all rows from the Pets table into Pets2 table.

It assumes that we’ve already created the Pets2 table and that it has the correct definition.

If it doesn’t exist or if it doesn’t have the correct definition, then you’ll get an error.

Insert Data from Multiple Tables

You can use this method to insert data from multiple tables.

INSERT INTO PetsTypesOwners
SELECT 
    p.PetId,
    p.PetName,
    p.DOB,
    pt.PetTypeId,
    pt.PetType,    
    o.OwnerId,
    o.FirstName,
    o.LastName,
    o.Phone,
    o.Email
FROM Pets p 
INNER JOIN PetTypes pt 
ON p.PetTypeId = pt.PetTypeId 
INNER JOIN Owners o 
ON p.OwnerId = o.OwnerId;

In this example I inserted data from three tables into one table called PetsTypesOwners.

Note that I explicitly listed each column. I did this because the destination column has less columns than the three tables combined. In this case, it’s because, if I had selected all columns, the query would have returned duplicate columns due to foreign keys having the same name and value as their primary keys.

If I wanted to include those duplicate columns, the destination column would need to include those duplicate columns, but with a different name for one of each duplicate pair.

Here’s an example of selecting all columns (I’ll select them explicitly here, so you can see their names):

INSERT INTO PetsTypesOwners2
SELECT 
    p.PetId, 
    p.OwnerId, 
    p.PetTypeId,
    p.PetName,
    p.DOB,
    pt.PetTypeId,
    pt.PetType,    
    o.OwnerId,
    o.FirstName,
    o.LastName,
    o.Phone,
    o.Email
FROM Pets p 
INNER JOIN PetTypes pt 
ON p.PetTypeId = pt.PetTypeId 
INNER JOIN Owners o 
ON p.OwnerId = o.OwnerId;

You can see that there are two columns called OwnerId and two called PetTypeId, each from a different table. These are the primary keys and foreign keys of their respective tables.

Given those columns are duplicates, I only included one of each in the previous example. But for the purpose of this demo, I’ve included them in this example.

So to cater for this new INSERT INTO... SELECT statement, we need our destination table to have the correct number of columns, and with a compatible definition.

Therefore, our destination table’s definition might look something like this:

CREATE TABLE PetsTypesOwners2 (
    PetId       int NOT NULL PRIMARY KEY,
    PetPetType  varchar(60) NOT NULL,
    PetOwnerId  int NOT NULL,
    PetName     varchar(60) NOT NULL,
    DOB         date NULL,
    PetTypeId   int NOT NULL,
    PetType     varchar(60) NOT NULL,
    OwnerId     int NOT NULL,
    FirstName   varchar(60) NOT NULL,
    LastName    varchar(60) NOT NULL,
    Phone       varchar(20) NOT NULL,
    Email       varchar(254)
);

In this case, I prefixed one of each duplicate column with Pet. Specifically, I created a PetPetTypeId (which maps to the p.OwnerId column in the query) and PetOwnerId column (which maps to the p.PetTypeId column in the query).

It’s important to note though, that it doesn’t matter what you call your columns in the destination table. The INSERT INTO... SELECT statement doesn’t care about that. The INSERT INTO... SELECT statement is only interested in the column order.

Insert Data from a View

You can use the same INSERT INTO... SELECT syntax to insert data into a table from a view.

Here’s a quick example:

INSERT INTO PetTypeCount
SELECT * FROM vPetTypeCount;

In this case, vPetTypeCount is a view, and I’m inserting its contents into a table called PetTypeCount.

Insert Data from a Stored Procedure

Some DBMSs (such as SQL Server) can also use the same INSERT INTO... SELECT syntax to insert data into a table from a stored procedure.

Here’s a quick example:

INSERT INTO PetById
EXEC uspGetPetById 2;

Again, this assumes that we have a destination table with the correct definition.

The SELECT INTO Statement

Depending on your DBMS, there’s also a SELECT INTO statement, that you can use to automatically create a new table based on the result set, then insert that result set into it.