DbSchema Database Designer


Publish on DbSchema Blog >>>

SQL, or Structured Query Language, is the standard language for interacting with databases. One of its fundamental statements is the SELECT INTO statement. This article will dive deep into the mechanics, syntax, and best practices related to the SQL SELECT INTO statement. By the end of this guide, you’ll have a thorough understanding of this topic, be able to use it efficiently, and avoid common pitfalls.

1. SQL SELECT INTO Statement: An Introduction

What is the SQL SELECT INTO Statement?

The SELECT INTO statement is used to copy data from one table and insert it into another new table. This can be especially useful when backing up data, creating a copy for testing, or transferring data between databases.


SELECT column1, column2, ...
INTO newtable
FROM oldtable;


Consider the following Students table:

ID Name Age
1 Alice 20
2 Bob 22
3 Charlie 21

To create a new table Backup_Students with the same data:

INTO Backup_Students
FROM Students;


A new table named Backup_Students will be created with the same columns and data as the Students table.

2. Difference between SQL SELECT vs. SELECT INTO

Purpose Retrieves data from a table Copies data from one table to a new table
New table creation Doesn’t create a new table Creates a new table
Data modification Doesn’t modify data Doesn’t modify original data, but copies it
Syntax SELECT column1, column2 FROM table; SELECT column1, column2 INTO newtable FROM table;

3. SQL SELECT INTO – Insert Data from Multiple Tables

Sometimes, you might need to create a new table by extracting data from multiple existing tables.


Consider two tables, Students and Courses:


ID Name
1 Alice
2 Bob


ID CourseName
1 Math
2 History

To combine data from both tables into a new table StudentCourses:

SELECT Students.ID, Students.Name, Courses.CourseName
INTO StudentCourses
FROM Students, Courses
WHERE Students.ID = Courses.ID;


StudentCourses table:

ID Name CourseName
1 Alice Math
2 Bob History

In the StudentCourses table, data from both the Students and Courses tables is combined based on the matching ID.

4. SELECT INTO with a WHERE Condition

You can use the WHERE clause with SELECT INTO to filter which data gets copied.


Using the earlier Students table, suppose we want to create a new table containing only students older than 21:

INTO Older_Students
FROM Students
WHERE Age > 21;


Older_Students table:

ID Name Age
2 Bob 22

Only Bob, who is older than 21, is copied to the Older_Students table.

5. Common Mistakes and How to Avoid Them

  • Overwriting Data: Ensure the destination table doesn’t already exist. If it does, the SELECT INTO will fail.
  • No Filtering: Using SELECT INTO without a WHERE clause will copy all data. Ensure you’re copying only the necessary data to avoid memory issues.
  • Column Mismatch: Ensure the columns in the SELECT statement match the destination table’s columns in both order and data type.

6. Frequently Asked Questions (FAQs)

Q: Can I use SELECT INTO to copy data into an existing table?
A: No, SELECT INTO creates a new table. To insert data into an existing table, use the INSERT INTO statement.

Q: Does SELECT INTO copy constraints like primary keys, indexes, etc.?
A: No, it only copies data and column definitions. Constraints need to be added manually to the new table.

7. Practice Questions

  1. Write a SELECT INTO query to copy all students with the name “Alice” into a new table.
  2. Create a new table combining data from tables Teachers and Subjects, ensuring both tables have a common TeacherID.
  3. Using the Orders table, create a backup table for all orders placed in the year 2022.


In conclusion, the SELECT INTO statement is a powerful tool in SQL, allowing for efficient data backup, testing, and transfer. By understanding its syntax and best practices, you can ensure data integrity and streamlined database operations. Remember always to back up your data and test your queries in a safe environment before applying them to production databases.

Happy Querying !!

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.