SQL 201-SELECT INTO

I’ve already shown you how to create a table, and how to INSERT data into that table.  But did you know you can do both in one statement?  The SELECT INTO statement selects data from one table and inserts it into a different table.  I use the SELECT INTO statement is to create backup copies of tables, but you may also use it to select part of one table into a separate table, or for any other reason you can come up with!

SQL SELECT INTO Syntax

We can select all columns into the new table:

SELECT *
INTO new_tableName
FROM old_tableName

Or we can select only the columns we want into the new table:

SELECT column_name(s)
INTO new_tableName
FROM old_tableName

SQL SELECT INTO Example

Make a Backup Copy – Now we want to make an exact copy of the data in our “Persons” table.

We use the following SQL statement:

SELECT *
INTO Persons_Backup
FROM Persons

We can also copy only a few fields into the new table:

SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons

SQL SELECT INTO – With a WHERE Clause

We can also add a WHERE clause.

The following SQL statement creates a “Persons_Backup” table with only the persons who lives in the city “Sandnes”:

SELECT LastName,Firstname
INTO Persons_Backup
FROM Persons
WHERE City='Charlotte'

SQL SELECT INTO – Joined Tables

Selecting data from more than one table is also possible.

The following example creates a “Persons_Order_Backup” table contains data from the two tables “Persons” and “Orders”:

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id

Let me know if you have any questions. I’m here to help you learn as much as you want about Microsoft SQL!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

1 comment

Leave a comment

Your email address will not be published. Required fields are marked *