T-SQL 101: 139 Selecting rows into a new SQL Server table

In a previous post, I did an INSERT followed by a SELECT. When I do that, the table needs to already exist.

But what if I want to take the rows returned from a SELECT statement and use them to create a new table?

That’s what a SELECT INTO statement does. In the example above, I’ve taken a distinct list of OrderID and OrderContact values and used them to create a new dbo.OrderContacts table. If the table already exists, the statement will fail.

This will create a new permanent table. You’ll often see people using this to create a temporary table.

Creating tables this way is easy, but it doesn’t let you control much about the way the table is created. If you want to exert control over that, you’re probably better to create the table and then use an INSERT SELECT statement instead.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-04-03