SQL Server Tutorialprovides basic and advanced concepts of C# for beginners and professionals.

  1. Home
  2. SQL Server Tutorial

SQL Select Into Statement

Back to: SQL Server Tutorial

In Microsoft SQL Server (MS SQL), the SELECT INTO statement is used to create a new table and insert data into it based on the result of a SELECT query. Here’s a more detailed look at how to use SELECT INTO in SQL Server:

Basic Syntax

SELECT column1, column2, ... INTO new_table FROM existing_table WHERE condition;

Example

Let's say you have an existing table named employees and you want to create a new table named high_salaries that contains employees earning more than $100,000. The SELECT INTO statement would look like this:

SELECT employee_id, first_name, last_name, salary INTO high_salaries FROM employees WHERE salary > 100000;

In this example:

  • employee_id, first_name, last_name, and salary are the columns being selected.
  • high_salaries is the name of the new table that will be created.
  • employees is the existing table from which data is being selected.
  • The WHERE clause filters rows to include only those where the salary exceeds $100,000.

Points to Consider

  1. Table Creation: The SELECT INTO statement will create the new table high_salaries with columns and data types that match those of the selected columns from the employees table.

  2. No Existing Table: If a table named high_salaries already exists, the SELECT INTO statement will fail. You can use INSERT INTO to add data to an existing table if needed.

  3. Indexes and Constraints: The new table will not inherit indexes, primary keys, foreign keys, or any constraints from the source table. You will need to add these manually if required.

  4. Schema: The new table will be created in the same schema as the table being queried. If you need to specify a different schema, you might need to use explicit CREATE TABLE statements or other methods.

  5. Data Types: Data types of the columns in the new table will be automatically inferred from the data types of the selected columns.

Additional Example

If you need to copy the structure of a table without the data, you can use a TOP 0 clause to select zero rows:

SELECT TOP 0 * INTO new_table_structure FROM existing_table;

This creates a new table new_table_structure with the same schema as existing_table but without any data.

Error Handling

  • Duplicate Table Error: If you try to create a table that already exists, you’ll encounter an error. To avoid this, you might need to drop the table first or use a different name.

  • Permissions: Ensure that you have the required permissions to create tables and perform the SELECT INTO operation.

This approach is particularly useful for quickly making backups, creating temporary tables for analysis, or transferring data between tables.

Scroll to Top