SQL Server SELECT INTO in Stored Procedures

In SQL Server, SELECT ... INTO new_table creates a new table and fills it with the rows returned by a query. Use it when the destination table does not yet exist, such as when making a snapshot, staging query results, or building a session-local temporary table inside a stored procedure. If the table already exists, use INSERT INTO ... SELECT instead.

What SQL Server SELECT INTO Does

SELECT ... INTO combines two actions:

  1. Creates a new table.
  2. Inserts the query result into that table.

The INTO clause belongs between the select list and the FROM clause:

SELECT
    column1,
    column2
INTO schema.new_table
FROM schema.source_table;

The target must be new. SQL Server returns an error if schema.new_table already exists.

The new table’s columns come from the expressions in the SELECT list. This is useful for quick copies, filtered snapshots, query staging, reporting extracts, and temporary result sets. It is not a full schema-cloning tool: indexes, primary keys, foreign keys, check constraints, defaults, triggers, and most other table-level objects are not copied.

How to Create a New Table With SELECT INTO

Choose the pattern that fits the destination before you write the query.

Goal Use This Pattern Why
Create a new persistent snapshot SELECT ... INTO reporting.TableName Creates and loads a new table in one statement
Fill a table that already exists INSERT INTO reporting.TableName ... SELECT ... SELECT INTO cannot use an existing target
Stage rows only during one procedure execution SELECT ... INTO #TempTable The local temporary table is scoped to the procedure session
Preserve an exact schema with constraints and indexes CREATE TABLE followed by INSERT INTO ... SELECT You control every column and table property
Create the table in another database INTO DatabaseName.schema.TableName Uses a fully qualified target name

Step 1: Choose the Target Schema and Check Permissions

Pick an explicit schema-qualified name rather than relying on the default schema:

INTO reporting.OpenOrders

For a permanent target table, the executing principal needs:

  • CREATE TABLE permission in the database.
  • ALTER SCHEMA permission on the schema that will own the table.
  • SELECT permission on the source tables or views.

Read-only access to the source is not enough to create the destination. SQL Server requires both CREATE TABLE and ALTER SCHEMA for the destination schema, as documented in Microsoft’s SELECT reference.

Create the destination schema first if your database does not already have one:

CREATE SCHEMA reporting;

Avoid using a generic table name such as Results or TempData for a permanent target. A second execution will fail if the first table still exists, and concurrent procedure executions can collide over the same name.

Step 2: Define the Columns Deliberately

The SELECT list defines the target table’s column order and metadata. Each new column gets the corresponding expression’s name, data type, nullability, and value.

Use aliases for calculated expressions and columns whose source names are not suitable for the new table:

SELECT
    o.OrderID,
    o.CustomerID,
    CAST(o.OrderDate AS date) AS OrderDate,
    o.TotalDue AS TotalAmount
INTO reporting.OpenOrders
FROM Sales.Orders AS o
WHERE o.Status = N'Open';

This creates reporting.OpenOrders with four columns in the displayed order:

  • OrderID
  • CustomerID
  • OrderDate
  • TotalAmount

Aliases matter because an unnamed or poorly named expression can produce an unhelpful target-column name. Treat the select list as the schema definition for the new table.

Step 3: Run the Copy Query

Run the statement after confirming that the target name is unused and that the filter returns the intended rows.

SELECT
    o.OrderID,
    o.CustomerID,
    CAST(o.OrderDate AS date) AS OrderDate,
    o.TotalDue AS TotalAmount
INTO reporting.OpenOrders
FROM Sales.Orders AS o
WHERE o.Status = N'Open';

SQL Server creates the table before loading the returned rows. The operation is a write operation, even though its source is a SELECT query.

If you need a new table in another database on the same SQL Server instance, use a three-part target name:

SELECT
    o.OrderID,
    o.CustomerID,
    o.OrderDate,
    o.TotalDue
INTO ArchiveDatabase.reporting.OpenOrders
FROM Sales.Orders AS o
WHERE o.Status = N'Open';

Step 4: Validate the New Table and Add Needed Objects

Start by comparing the expected row count with the target row count.

SELECT COUNT_BIG(*) AS SourceRowCount
FROM Sales.Orders AS o
WHERE o.Status = N'Open';
SELECT COUNT_BIG(*) AS TargetRowCount
FROM reporting.OpenOrders;

Then inspect the generated columns:

SELECT
    c.column_id,
    c.name AS column_name,
    t.name AS data_type,
    c.max_length,
    c.is_nullable
FROM sys.columns AS c
INNER JOIN sys.types AS t
    ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(N'reporting.OpenOrders')
ORDER BY c.column_id;

Add indexes and constraints only after verifying that the copied data supports them. For example, if OrderID is unique in the new table, add a primary key:

ALTER TABLE reporting.OpenOrders
ADD CONSTRAINT PK_OpenOrders
PRIMARY KEY (OrderID);

For a frequently filtered or joined column, add an appropriate index:

CREATE INDEX IX_OpenOrders_CustomerID
ON reporting.OpenOrders (CustomerID);

Use SELECT INTO Inside a Stored Procedure

A local temporary table is usually the safest SELECT INTO target inside a stored procedure. It avoids persistent-table name conflicts, supports repeated execution, and is removed when the procedure finishes.

This procedure stages a filtered result in #RegionalOpenOrders, then returns it:

CREATE OR ALTER PROCEDURE reporting.GetRegionalOpenOrders
    @Region nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        o.OrderID,
        o.CustomerID,
        o.OrderDate,
        o.TotalDue AS TotalAmount
    INTO #RegionalOpenOrders
    FROM Sales.Orders AS o
    WHERE o.Status = N'Open'
      AND o.Region = @Region;

    SELECT
        OrderID,
        CustomerID,
        OrderDate,
        TotalAmount
    FROM #RegionalOpenOrders
    ORDER BY OrderDate, OrderID;
END;

Execute it like this:

EXEC reporting.GetRegionalOpenOrders
    @Region = N'West';

A local temporary table name begins with #. SQL Server keeps it available for the procedure’s session and removes it when the procedure ends.

A permanent table created with SELECT INTO inside a procedure is different. The first run can succeed, but later runs fail unless the procedure handles the existing table. For repeatable ETL or reporting loads, define the destination with CREATE TABLE and load it with INSERT INTO ... SELECT; that gives you a stable schema, reusable indexes, and predictable reruns.

A regular SELECT INTO target name cannot be supplied through a variable. Building dynamic SQL to vary table names is possible, but it adds identifier-validation, injection-risk, permissions, and concurrency concerns. Prefer a fixed table name, a temporary table, or a designed staging-table process.

Handle Joins, Identity Columns, and Filegroups

Joins Can Change Identity Behavior

A query containing a join can create a new table normally:

SELECT
    c.CustomerID,
    c.CustomerName,
    a.City,
    a.StateCode
INTO reporting.CustomerAddresses
FROM Sales.Customers AS c
INNER JOIN Sales.Addresses AS a
    ON a.AddressID = c.AddressID;

However, an existing IDENTITY property does not transfer when the SELECT INTO query contains a join. The resulting column can contain the original numeric values, but it is no longer an identity column.

If the joined result needs a new identity column, project one explicitly in the select list:

SELECT
    IDENTITY(INT, 100, 5) AS AddressID,
    c.CustomerID,
    c.CustomerName,
    a.City,
    a.StateCode
INTO reporting.CustomerAddresses
FROM Sales.Customers AS c
INNER JOIN Sales.Addresses AS a
    ON a.AddressID = c.AddressID;

Here, INT is the identity data type, 100 is the seed, 5 is the increment, and AddressID is the output column name. Choose those values to match the intended key design.

Computed columns also become stored values in the new table. If the source select list contains UnitPrice * Quantity AS LineTotal, the target gets a regular LineTotal column containing the values calculated when the statement ran, not a computed-column definition.

Choose a Filegroup When Storage Placement Matters

Without an explicit filegroup, SQL Server creates the new table in the target database’s default filegroup.

Use ON filegroup when the new table must live in a chosen filegroup:

SELECT
    o.OrderID,
    o.CustomerID,
    o.OrderDate,
    o.TotalDue
INTO reporting.OpenOrdersArchive
ON FG_ReportingArchive
FROM Sales.Orders AS o
WHERE o.Status = N'Open';

The named filegroup must already exist or SQL Server returns an error. The ON filegroup option is available in SQL Server 2016 (13.x) SP2 and later.

Plan for Logging and Large Loads

A table-creating query is a write operation with operational consequences, not a read-only report.

Large SELECT INTO operations can use substantial transaction-log space. The recovery model, indexes, source-query complexity, concurrent workload, and any functions in the query all affect the operational impact.

Microsoft’s minimal-logging example changes the AdventureWorks2025 database to the BULK_LOGGED recovery model before SELECT INTO, then returns it to FULL afterward. That sequence is a database recovery and backup decision, not a generic performance switch to apply casually. Plan the recovery-model change, required backups, maintenance window, and rollback approach with the team responsible for the database.

User-defined functions require special attention. On SQL Server 2016 (13.x) SP2 and later, a SELECT INTO statement that contains a UDF is fully logged. If a UDF performs no data access, defining it with SCHEMABINDING sets its derived UserDataAccess property to 0, which can allow minimal logging. If any referenced UDF has UserDataAccess set to 1, the SELECT INTO operation remains fully logged.

For large tables, test the query against representative data, estimate log growth, and make sure there is sufficient storage before running it in production. Microsoft documents these logging and filegroup behaviors in its SELECT INTO documentation.

Confirm the Result and Keep It Maintainable

Before treating the target as a usable table, confirm all of the following:

  • The row count matches the intended source filter.
  • Column names, data types, and nullability match the select-list expressions.
  • Identity behavior is correct, especially for join-based queries.
  • Required primary keys, foreign keys, defaults, checks, and indexes have been added.
  • The target table is in the intended database, schema, and filegroup.
  • The load procedure has a clear rerun and cleanup plan.

For a one-time snapshot, record the query that created the table and the source filter used. For a recurring load, replace ad hoc permanent SELECT INTO operations with an explicit table definition and a controlled INSERT INTO ... SELECT process.

For the next load, decide whether the result is a one-time snapshot, a temporary procedure result, or a reusable table. That choice determines whether SELECT INTO, #temp staging, or CREATE TABLE plus INSERT INTO ... SELECT is the cleanest design.

Frequently Asked Questions

Can SELECT INTO create an empty table?

Yes. Use a condition that returns no rows, such as WHERE 1 = 0. SQL Server creates the new table based on the select-list expressions but inserts no records.

SELECT
    o.OrderID,
    o.CustomerID,
    o.OrderDate
INTO reporting.EmptyOrderTemplate
FROM Sales.Orders AS o
WHERE 1 = 0;

Can SELECT INTO copy primary keys and indexes?

No. SELECT INTO creates columns and rows, but it does not recreate table-level objects such as primary keys, foreign keys, indexes, check constraints, defaults, or triggers. Use CREATE TABLE first when those definitions are part of the required result.

Can ORDER BY control the physical row order in a SELECT INTO table?

No. A table has no guaranteed physical or logical row order. Use ORDER BY whenever you read rows back and need a predictable presentation order.

Should I use SELECT INTO for a recurring ETL process?

Usually not for the final destination. SELECT INTO is excellent for one-time copies and temporary staging, but recurring processes are generally easier to maintain with a predefined table and INSERT INTO ... SELECT, because the schema, indexes, permissions, and rerun behavior are explicit.

Can a SELECT INTO query use a join?

Yes, but a join prevents an existing identity property from transferring to the new table. If the result needs a new identity key, define one in the select list, such as IDENTITY(INT, 100, 5) AS AddressID.