Selecting data into a new table

by Dave Andrews

If you have a series of data that you want to insert into a new table, or into a temporary table, it’s very simple. This can be used to create a simple backup of records, or to take a very long and complex query and save its results for future use. If you save into a table, that table won’t have any relationships that are in the existing table, just something to keep in mind.

Let’s say we have a query like this.

1
2
3
4
5
6
SELECT
     *
FROM
     Animals a
     INNER JOIN Animal_Details ad
          ON a.AnimalID = ad.AnimalID

This table will join together two tables and pull in the details of whatever animals are in the animals table. I just made this up quickly. Let’s have SQL save that to a new table.

1
2
3
4
5
6
7
8
SELECT
     *
INTO 
     AnimalDetailsCombined
FROM
     Animals a
     INNER JOIN Animal_Details ad
          ON a.AnimalID = ad.AnimalID

This query will create a brand new table called AnimalDetailsCombined and save the results of that query into the table. If you wanted to save to a temporary table, which would be deleted once the connection is closed, just add a hashtag (#) in front of the table name.

1
2
3
4
5
6
7
8
SELECT
     *
INTO 
     #AnimalDetailsCombined
FROM
     Animals a
     INNER JOIN Animal_Details ad
          ON a.AnimalID = ad.AnimalID

You now have a temporary table called #AnimalDetailsCombined with is the result of the query.