Copying data from one table to another is a common task in SQL Server. Whether you need to backup your data, create a new table with specific data, or simply move data between tables, there are multiple methods you can use to achieve this.

In this article, we will explore two popular methods for copying data from one table to another in SQL Server.

Method 1: INSERT INTO SELECT

The first method involves using the INSERT INTO SELECT statement. This method allows you to select data from one table and insert it into another table in a single statement.

Here is an example:

-- Create TestTable
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.Person
WHERE EmailPromotion = 2

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

-- Clean up the database
DROP TABLE TestTable

Method 2: SELECT INTO

The second method involves using the SELECT INTO statement. This method allows you to create a new table and insert data into it from another table in a single statement.

Here is an example:

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

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

-- Clean up the database
DROP TABLE TestTable

Both methods achieve the same result, but the choice between them depends on your specific requirements and preferences.

Copying data from one table to another is a powerful feature in SQL Server that can save you time and effort. Whether you are performing data migrations, creating backups, or simply manipulating data, these methods can help you accomplish your tasks efficiently.

What other SQL Server concepts or topics would you like to learn about? Let us know in the comments below!