Importing/Exporting Data  «Prev  Next»

Lesson 3 SQL SELECT INTO Statement
Objective Load data with the select into statement.

Loading Data with the SELECT INTO Statement in SQL Server 2025

Occasionally you'll want to load rows into a table that doesn't exist yet. The previous lesson's INSERT statement assumes the target table is already there; SELECT INTO does both jobs in a single statement, creating the table and populating it with a query's result set at the same time. It's a small statement with a lot of practical use: archiving, quick backups before a risky change, and building an intermediate result set partway through a more complex piece of work all lean on exactly this pattern.

It's worth being clear about what makes this statement different from the two you've already learned. INSERT needs a table that already exists; SELECT INTO builds that table for you. Where the two end up overlapping, and where they genuinely don't, is worth its own section further down in this lesson, once the syntax itself is out of the way.

Basic Syntax

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

column1, column2, ... are the columns being copied, new_table is the table SELECT INTO creates for you, existing_table is the source, and the WHERE clause is an optional filter, exactly like an ordinary SELECT. Everything that works in a regular SELECT's column list and WHERE clause, joins, computed expressions, functions, works here too; the only thing that changes is the INTO clause telling SQL Server to create a table out of the result rather than simply returning it to the caller.

Key Features

  • Automatic table creation: the new table's columns take their names and data types from the selected columns. A computed column or an expression in the select list can produce a data type that doesn't exactly match its source column, so it's worth checking the new table's definition with a quick look at its columns in SSMS's Object Explorer when the select list isn't a plain, direct column reference.
  • Data population: the result set is loaded into the new table as part of the same statement; there's no separate load step afterward, and no moment where the table exists but is empty.
  • No constraints, indexes, or triggers: none of these carry over from the source table. A PRIMARY KEY, a foreign key, a default constraint, any index at all, has to be added to the new table explicitly afterward if you want it there.
  • Nullability carries over, IDENTITY sometimes does: the new column keeps the same nullability as its source. The IDENTITY property is a little more conditional: it carries over too, but only when the select list is simple, no join, no UNION, the identity column named only once, not part of an expression, and not coming from a remote source. If any of those apply, the new column still gets the copied values, but comes out as an ordinary NOT NULL column rather than an auto-incrementing identity column.
  • A computed column becomes a plain value: if the source table has a computed column, or the select list itself computes an expression, the new table doesn't inherit the computation; it gets a regular column holding whatever that expression evaluated to at the moment the statement ran, frozen in place rather than recalculated later.

On the occasions you do need an identity column in the result and the select list isn't simple enough to inherit one automatically, the IDENTITY function in the select list builds one explicitly, with its own seed and increment:

SELECT IDENTITY(INT, 1, 1) AS ArchiveID,
       OrderID, CustomerID, OrderDate
INTO SalesOrdersArchive
FROM SalesOrders
WHERE OrderDate < '2025-01-01';

INSERT or SELECT INTO: Which One

Between this lesson and the last one, you now have two different ways to get a query's result set into a table, and it's worth being direct about when each one actually applies. INSERT ... SELECT, covered in the previous lesson, loads a query's result into a table that already exists, built with whatever columns, constraints, and indexes you set up for it ahead of time. SELECT INTO skips that setup step entirely, building the table's structure on the fly from the query itself. That makes SELECT INTO genuinely faster to write for a one-off table, but it also means you're accepting whatever structure the query happens to produce, with no constraints and no indexes, rather than a structure you designed deliberately.

In practice, that makes SELECT INTO the better fit for temporary, exploratory, or one-time work, a quick backup before a risky update, a scratch table for a complex report you're building, a snapshot you only need for the next hour. INSERT ... SELECT is the better fit once the destination table is something that's going to stick around and be queried regularly, where having a proper primary key and the right indexes in place from the start actually matters.

A reasonable rule of thumb: if you find yourself immediately adding a primary key and a couple of indexes to a table SELECT INTO just created, that's a sign the table was never really a throwaway in the first place, and building it with CREATE TABLE plus INSERT ... SELECT from the start would have been the more honest approach. SELECT INTO earns its keep specifically in the cases where you genuinely don't want to think about the table's structure in advance.

Where You'll Run This

Older material here walks through Azure Data Studio specifically, now retired, as this course covered in its very first module. SQL Server Management Studio is the standard place to run a SELECT INTO statement on Windows: connect to your instance, open a new query window against the right database, and run the statement, the same workflow you've already used throughout this series. On Linux or macOS, or if you simply prefer an editor-style workflow, the MSSQL extension for Visual Studio Code covers the same ground, as this course has already noted more than once. Nothing about the statement itself changes based on which tool you're running it from; SELECT INTO is plain T-SQL, and the tool is only the window you're typing it into.

An Example

Building on the archive example from the previous lesson, SELECT INTO is a natural fit for creating that archive table in the first place, rather than assuming it already exists:

SELECT OrderID, CustomerID, OrderDate, OrderTotal
INTO SalesOrdersArchive
FROM SalesOrders
WHERE OrderDate < '2025-01-01';

This single statement creates SalesOrdersArchive with columns matching the four selected, and loads it with every order placed before January 1, 2025. Note that the new table has none of SalesOrders's constraints, indexes, or triggers; if SalesOrdersArchive needs a primary key or an index of its own, perhaps on OrderDate, since that's the column archived reports are likely to filter on, that's a separate step after this one, something like:

ALTER TABLE SalesOrdersArchive
ADD CONSTRAINT PK_SalesOrdersArchive PRIMARY KEY (OrderID);

CREATE INDEX IX_SalesOrdersArchive_OrderDate
ON SalesOrdersArchive (OrderDate);

Running both statements back to back, create via SELECT INTO and then immediately add the primary key and index, is a perfectly normal pattern; nothing about this lesson suggests the two approaches are mutually exclusive. The point of calling out that constraints and indexes don't carry over automatically is simply that this second step is a deliberate choice you make afterward, not something that happens on its own.

Creating an Empty Table with the Same Schema

Adding a condition that can never be true creates the table's structure with no rows at all, a quick way to get a matching empty table ready for data you'll load some other way:

SELECT * INTO NewTable FROM ExistingTable WHERE 1 = 0;

This is a genuinely useful trick when you want an exact structural copy of an existing table, matching every column's name and data type, without committing to copying its data as well. A staging table built this way, for instance, lines up column for column with the table it's eventually meant to feed, which avoids a whole category of mapping mistakes that come from building the staging table's structure by hand instead.

Against the Inventory table built earlier in this series, the same technique produces a ready-to-load staging table without retyping a single column definition:

SELECT * INTO InventoryStaging FROM Inventory WHERE 1 = 0;

InventoryStaging comes out with the exact same StockNum, Cost, RetailPrice, QtyAvailable, and LastPriceChangeDate columns as Inventory itself, ready for a load process to populate before the real table gets updated from it.

Temporary Tables

SELECT INTO also works for temporary tables, by prefixing the new table's name with # for a local temporary table or ## for a global one:

SELECT * INTO #TempTable FROM ExistingTable WHERE condition;

Temporary tables live in tempdb rather than the current database. A local temporary table, the #TempTable above, is dropped automatically when the session that created it ends, and is only visible to that one session in the first place; it's a scratch space for whatever that session is working through right now, not something another connection can see at all. A global temporary table, created with ##, stays visible to every session until the last one referencing it closes, which makes it useful for sharing a working result set across more than one connection, at the cost of needing to actually think about cleanup rather than letting the session boundary handle it for you.

One platform nuance worth knowing: on Azure SQL Database specifically, a global temporary table's visibility is scoped to the single database it was created in, rather than across every database on the instance the way it works on an on-premises or VM-hosted SQL Server. It's a small distinction, but worth remembering if you ever move a global-temp-table-dependent script from one environment to the other and the behavior doesn't quite match what you expected.

Local temporary tables are the far more common of the two in practice. Because they're private to the session that created them, two different connections can both create a #Staging table in the same moment, each running its own load independently, with no risk of one session's work colliding with the other's. That isolation is exactly what makes them a safe default for the kind of scratch work this lesson has been describing throughout; reaching for a global temporary table is worth a second thought, since sharing state across sessions is also a way to introduce the exact kind of unexpected interaction a local temporary table avoids by design.

Considerations and Limitations

Creating a table at all requires CREATE TABLE permission in the target database; membership in the db_ddladmin or db_owner role covers this along with the other DDL permissions already discussed in this series. Performance-wise, SELECT INTO is minimally logged under the simple or bulk-logged recovery model, the same recovery-model distinction the previous lesson already covered, which can make a real difference for a large result set; under the full recovery model, it's still fully logged, the same as an ordinary INSERT.

A few genuine limitations are worth knowing before reaching for SELECT INTO:
  • It can't create a partitioned table directly, even if the source table is partitioned; the new table always lands on the default filegroup. A genuinely partitioned destination has to be built separately, with its partition scheme already in place, and loaded with INSERT ... SELECT instead.
  • It doesn't support specifying constraints or indexes as part of the statement itself, which is really the same point as this lesson's Key Features section, stated here as a limitation rather than a feature.
  • It can't be used with table variables or table-valued parameters as a target, and it can't create the new table on a remote server; those need to already exist with a defined structure before you can insert into them at all.

One more mechanical detail worth knowing: SELECT INTO is really two steps under the hood, creating the table and then inserting the rows, even though it's written as one statement. If the insert portion fails partway through, perhaps a data type that doesn't fit, SQL Server rolls back the rows that were inserted, but the newly created, now-empty table is left behind rather than being rolled back along with them. Wrapping the whole statement in an explicit transaction, the same pattern this course's previous lesson already covered, is the way to make the entire operation genuinely all-or-nothing, table creation included. It's also worth remembering that an ORDER BY on the SELECT side controls which rows get selected when paired with TOP, as the previous lesson covered, but doesn't guarantee the physical order rows end up stored in once they're in the new table.

Best Practices

Listing columns explicitly, rather than using SELECT *, keeps the new table's schema intentional rather than whatever happened to be in the source table that day; it also means the statement keeps working correctly if someone adds a column to the source table later, instead of silently pulling in a column you never meant to copy.

Adding indexes and constraints immediately after creating the table, rather than leaving it bare, keeps it from becoming a performance or integrity problem the first time something queries or writes to it at scale. A table created purely for a single, throwaway query doesn't need this; a table that's going to stick around, even as a temporary table referenced repeatedly within a long session, usually does.

Consistent naming, SalesOrdersArchive rather than something generic like Temp1 or NewTable, pays off the first time someone other than you, or you yourself three months later, has to figure out what the table is for and whether it's still needed.

One last habit worth building: once a SELECT INTO-created table has done its job, whether that's a one-time report, a staging table that's already fed its target, or a backup that's confirmed safe to discard, drop it. These tables accumulate quietly in a database that's otherwise well maintained precisely because they're so easy to create, and a database full of forgotten tables named after whoever created them and when is a genuinely common problem in environments that use this statement heavily without a corresponding habit of cleaning up afterward.

What's Ahead

The next lesson moves from these smaller, T-SQL-only tools to BULK INSERT, built specifically for loading large volumes of data quickly from a file, the kind of job where neither INSERT ... SELECT nor SELECT INTO is really the right tool once the row count gets large enough. Between INSERT, covered in the previous lesson, and SELECT INTO here, you now have the two most common ways to move a result set into a table using nothing but plain T-SQL; the tools ahead trade some of that simplicity for the speed a genuinely large load actually needs.

SEMrush Software 3 SEMrush Banner 3