The SELECT INTO statement in SQL Server is used to create a new table and insert data into it based on the result of a SELECT query. This can be particularly useful for creating backup copies of tables, creating test tables, or copying data for analysis.
Introduction to SELECT INTO
The SELECT INTO statement combines the functionalities of SELECT and INSERT into one statement. It creates a new table and populates it with the result set from the SELECT statement.
Basic Syntax of SELECT INTO
SELECT column1, column2, …
INTO new_table
FROM existing_table
WHERE condition;
column1, column2, …: The columns you want to select.
new_table: The name of the new table that will be created.
existing_table: The table from which to select data.
condition: An optional WHERE clause to filter rows.
Examples
Copying All Data into a New Table
Consider a table Products with the following data:
To create a new table ProductsBackup with all the data from Products:
SELECT ProductID, ProductName, Price, Quantity
INTO ProductsBackup
FROM Products;
Example : Creating a New Table with Filtered Data
To create a new table ExpensiveProducts with only the products that have a price greater than 1.00:
SELECT ProductID, ProductName, Price, Quantity
INTO ExpensiveProducts
FROM Products
WHERE Price > 1.00;
Example: Creating a New Table with Computed Columns
You can also include computed columns in the new table. For example, to create a new table ProductsWithValue with a computed column TotalValue:
SELECT ProductID, ProductName, Price, Quantity, (Price * Quantity) AS TotalValue
INTO ProductsWithValue
FROM Products;
Copying Data from Multiple Tables
You can use joins to combine data from multiple tables into a new table. Assume we have two tables Orders and OrderDetails.
To create a new table OrderSummaries that includes order details
SELECT o.OrderID, o.CustomerID, o.OrderDate, od.ProductID, od.Quantity
INTO OrderSummaries
FROM Orders o
JOIN OrderDetails od ON o.OrderID = od.OrderID;
Handling Identity Columns
If the source table has an identity column and you want to retain the identity property in the new table, you’ll need to use a workaround, as SELECT INTO does not copy identity properties.
Example
— Create the new table without the identity property
SELECT * INTO TempProducts FROM Products WHERE 1 = 0;— Add the identity property
ALTER TABLE TempProducts ADD ProductID INT IDENTITY(1,1);— Insert the data
INSERT INTO TempProducts (ProductName, Price, Quantity)
SELECT ProductName, Price, Quantity FROM Products;
Performance Considerations
Transaction Logs: SELECT INTO can be minimally logged, making it more efficient for large data sets compared to INSERT INTO … SELECT.
Indexes and Constraints: SELECT INTO does not copy indexes, constraints, or triggers from the source table. These need to be added separately if required.
Error Handling
Using TRY…CATCH blocks can help manage potential errors during the creation and population of the new table.
Example:
BEGIN TRY
SELECT ProductID, ProductName, Price, Quantity
INTO ProductsBackup
FROM Products;
END TRY
BEGIN CATCH
PRINT ‘Error occurred: ‘ + ERROR_MESSAGE();
END CATCH;
The SELECT INTO statement is a powerful and efficient way to create and populate new tables in SQL Server. It simplifies the process of copying data and can be very useful for creating backups, test tables, and for data analysis.