Select into Statement using SQL-Server

Occasionally, you will want to add rows to a table that does not exist. The select into statement will create a table and load the result set into the table.
The following section discusses the `SELECT INTO` statement tailored for deploying Microsoft SQL Server 2022 using Azure Data Studio.
Creating Tables with `SELECT INTO` in SQL Server 2022 Using Azure Data Studio
The `SELECT INTO` statement in SQL Server 2022 allows you to create a new table and populate it with data from an existing table or query result in a single operation. This approach is particularly useful for tasks such as data archiving, backup creation, and generating intermediate result sets for complex queries.
Syntax

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

1. column1, column2, ...: Columns to be copied.
2. new_table: The name of the new table to be created.
3. existing_table: The source table from which data is selected.
4. condition: Optional criteria to filter rows.

• Automatic Table Creation: The new table is created with columns that match the names and data types of the selected columns.
• Data Population: Data is inserted into the new table as part of the operation.
• No Constraints or Indexes: The new table does not inherit constraints, indexes, or triggers from the source table.

Usage in Azure Data Studio
To utilize `SELECT INTO` in Azure Data Studio:

1. Connect to Your Database:
   • Open Azure Data Studio.
   • Connect to your SQL Server 2022 instance.
   • Navigate to the desired database.
2. Open a New Query Editor:
   • Right-click on the database and select "New Query".([Microsoft Learn][3])
3. Execute the SELECT INTO Statement:
   • Write and run your SELECT INTO statement in the query editor.

Here's an example of a SELECT INTO statement using SQL Server 2022 and Azure Data Studio:[1]

-- Create a new table 'NewEmployees' by copying selected data from 'Employees'
SELECT EmployeeID, FirstName, LastName, HireDate
INTO NewEmployees
FROM Employees
WHERE DepartmentID = 3;

Explanation:

• This query selects EmployeeID, FirstName, LastName, and HireDate from the Employees table.
• The INTO NewEmployees clause creates a new table named NewEmployees with the selected columns.
• The WHERE clause filters for employees in Department 3.
• Run this in Azure Data Studio connected to a SQL Server 2022 instance.

Notes:

• The new table (NewEmployees) inherits the data types of the selected columns but does not copy constraints, indexes, or triggers.
• Ensure you have appropriate permissions to create tables in the target database.

SELECT FirstName, LastName
INTO EmployeeBackup
FROM Employees
WHERE Department = 'Sales';

This command creates a new table `EmployeeBackup` containing first and last names of employees from the Sales department.
Considerations

• Permissions: Ensure you have the necessary permissions to create tables in the target database.
• Data Types: Be aware that computed columns or expressions may result in data types that differ from the source columns.
• Performance: SELECT INTO operations are minimally logged in the simple or bulk-logged recovery models, which can enhance performance for large data transfers.

Limitations:

• Cannot create partitioned tables directly.
• Does not support specifying constraints or indexes during creation.
• Cannot be used with table variables or table-valued parameters.

To create a new table with the same schema but without data:

SELECT * INTO NewTable FROM ExistingTable WHERE 1 = 0;

This technique is useful for preparing a table structure for future data insertion.
Temporary Tables
You can create temporary tables using `SELECT INTO` by prefixing the table name with `#` (local temporary table) or `##` (global temporary table):

SELECT * INTO #TempTable FROM ExistingTable WHERE Condition;

Temporary tables are stored in the `tempdb` database and are automatically dropped when the session ends (for local temporary tables) or when all sessions referencing them are closed (for global temporary tables).
Best Practices

• Explicit Column Selection: Specify column names instead of using SELECT * to ensure clarity and control over the new table's schema.
• Indexing and Constraints: After creating the new table, add necessary indexes and constraints to maintain data integrity and query performance.
• Naming Conventions: Use clear and consistent naming conventions for new tables to enhance maintainability.

Example: Creating a Backup Table

SELECT * INTO OrdersBackup FROM Orders WHERE OrderDate < '2023-01-01';

This statement creates a new table `OrdersBackup` containing all orders placed before January 1, 2023.
By leveraging the SELECT INTO statement in SQL Server 2022 through Azure Data Studio, you can efficiently create and populate new tables for various data management tasks.

[1] Azure Data Studio: Azure Data Studio is a lightweight, cross-platform database tool that can connect to various Azure Cloud Database services like Azure SQL Database and Azure Cosmos DB. It provides a user-friendly interface for querying, designing, and managing these cloud-based databases, offering features like IntelliSense and integrated terminal for efficient database development and administration within the Azure ecosystem.

[2] Global temporary tables: Global temporary tables in the context of Azure Cloud Database services, specifically Azure SQL Database, are temporary tables stored in the `tempdb` database that are accessible to all user sessions within the same **single database**. Unlike in traditional SQL Server where they are visible across all databases on the same instance, their scope in Azure SQL Database is limited to the individual database they are created in.