Microsoft SQL Server

Chapter 6 - DML (Data Manipulation Language)

SELECT INTO

In Microsoft SQL Server, the SELECT INTO statement is used to create a new table from the result of a query. It selects data from one or more existing tables and inserts the result set into a new table that is automatically created. This is a quick way to back up data, create copies of tables, or store query results for future use. For beginners, it's a powerful feature, as it combines both SELECT and INSERT into a single operation.

Here's how you can use SELECT INTO with a detailed breakdown:

Basic Syntax of SELECT INTO:
- The syntax for creating a new table and inserting data using SELECT INTO is:
```
SELECT column1, column2, column3, ...
INTO new_table
FROM existing_table
WHERE condition;
```

Steps for Using SELECT INTO:
1. Choosing the source table:
  - Begin by selecting the existing table from which you want to copy data. This table will be the source of the data.
2. Selecting the columns:
  - You can specify which columns you want to copy from the source table. If you want to copy all columns, you can use SELECT *.
3. Specifying the new table:
  - After the INTO keyword, define the name of the new table that will be created and populated with the query results.
4. Applying filters:
  - If necessary, you can apply a WHERE clause to filter the data being selected and inserted into the new table.

Example - Creating a Backup Table for Customers:
- Let’s say you want to create a backup of your Customers table, copying all customer data into a new table called CustomersBackup:
```
SELECT *
INTO CustomersBackup
FROM Customers;
```
- This will create the CustomersBackup table with the same structure and data as the original Customers table.

Example - Creating a Table with Filtered Data:
- You may want to create a table that only contains customers from a specific country, for example, customers from the USA:
```
SELECT CustomerID, CustomerName, Email
INTO US_Customers
FROM Customers
WHERE Country = 'USA';
```
- This query creates a new table US_Customers that contains only customers from the USA.

Inserting Data from Multiple Tables:
- You can also use joins to insert data from multiple tables into a new table. For example, if you want to create a table that contains product names and their total sales, you can write:
```
SELECT P.ProductName, SUM(S.Quantity) AS TotalSales
INTO ProductSales
FROM Products P
JOIN Sales S ON P.ProductID = S.ProductID
GROUP BY P.ProductName;
```
- This query creates a new table ProductSales with product names and their respective total sales.

Copying Data with Calculations:
- You can also perform calculations or transformations during the data selection:
```
SELECT ProductID, ProductName, Price * 1.1 AS NewPrice
INTO UpdatedProducts
FROM Products;
```
- Here, a new table UpdatedProducts is created, where the price of each product is increased by 10%.

Best Practices:
- Ensure the new table doesn't already exist: The SELECT INTO statement will fail if the table already exists, so it's a good idea to verify or use IF NOT EXISTS conditions.
- Be mindful of data types: When the new table is created, it automatically inherits the data types of the selected columns from the source table.
- Test your query first: Always run the SELECT part of the query alone first to ensure it returns the expected results before adding the INTO clause.

By understanding these concepts and following the examples, beginners will be able to effectively use the SELECT INTO statement in SQL Server.

Tansy SQL Course - SELECT INTO - Video Thumbnail

Previous Next

Comments(0 comments)

Comments Not Found

Microsoft SQL Server

SELECT INTO

Most visited