Using SELECT INTO to Create Tables in SQL Server

When working with SQL Server, you might want to quickly create a new table from the results of a query. Instead of manually defining the table structure, you can use the SELECT INTO statement to generate a table on the fly.

---

🔎 What is SELECT INTO?

The SELECT INTO statement allows you to:

• Create a new table based on the structure of your SELECT query.
• Copy data into that new table at the same time.

This is particularly useful for:

• Creating backup or archive tables.
• Extracting subsets of data into temporary or staging tables.
• Quickly prototyping queries without building schema manually.

---

🖥️ Syntax

SELECT column1, column2, ...
INTO NewTable
FROM ExistingTable
WHERE condition;

• NewTable is the name of the table that will be created.
• The new table will have the same column definitions (names and data types) as the selected columns.
• The query will insert the selected rows into the new table.

---

🧑‍💻 Example 1: Copy Entire Table

SELECT *
INTO CustomersBackup
FROM Customers;

This creates a new table CustomersBackup with the same structure as Customers and copies all rows into it.

---

🧑‍💻 Example 2: Copy Subset of Data

SELECT CustomerID, CompanyName, Country
INTO US_Customers
FROM Customers
WHERE Country = 'USA';

This creates a table US_Customers containing only customers from the USA.

---

🧑‍💻 Example 3: With Joins

SELECT 
    o.OrderID, 
    c.CustomerName, 
    o.OrderDate
INTO OrdersSummary
FROM Orders o
INNER JOIN Customers c 
    ON o.CustomerID = c.CustomerID;

Here, the new table OrdersSummary is created based on a join query.

---

⚠️ Important Notes

• The new table must not already exist; otherwise, you’ll get an error.
• Constraints, indexes, triggers, and relationships are not copied—only column definitions and data.
• If you need the schema only (no rows), use a WHERE 1=0 filter:

SELECT *
INTO EmptyTable
FROM Customers
WHERE 1 = 0;

---

✅ When to Use SELECT INTO

• Quick backups.
• Data archiving.
• Staging/intermediate datasets in ETL processes.
• Rapid testing and development.

---

By using SELECT INTO, you can speed up your SQL workflows and avoid repetitive table creation steps.