When working with SQL Server, there are often scenarios where you need to insert data from one table to another table efficiently. In this article, we will discuss two different methods that can be used to achieve this, without the need for cursors.

Method 1: INSERT INTO SELECT

This method is used when the destination table already exists in the database, and you want to insert data into it from another table. The columns listed in the INSERT clause and the SELECT clause should match, although it is not required. However, it is recommended to list them for readability and scalability purposes.

Here is an example:

USE AdventureWorks;

-- Create a test table
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100));

-- Insert data into TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2;

-- Verify the data in TestTable
SELECT FirstName, LastName
FROM TestTable;

-- Clean up the database
DROP TABLE TestTable;

Method 2: SELECT INTO

This method is used when the destination table does not exist and needs to be created dynamically based on the selected columns from another table. The new table will have the same data types as the selected columns.

Here is an example:

USE AdventureWorks;

-- Create a new table and insert data into it using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2;

-- Verify the data in TestTable
SELECT FirstName, LastName
FROM TestTable;

-- Clean up the database
DROP TABLE TestTable;

Both of the above methods can also be used with temporary tables (global and local) in the database. If you need to insert multiple rows using only one INSERT statement, you can refer to the article “SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL”.

By using these efficient methods, you can improve the performance of your SQL Server queries and avoid the need for cursors. It is recommended to choose the method that best suits your specific requirements and database structure.