How to Copy data from one table to another in SQL

INSERT INTO EmployeeDetails
(EmployeeID, FirstName, LastName, Salary)
SELECT EmployeeID, FirstName, LastName, Salary FROM Employees

If the column names and order are the same in both tables (and there are no identity columns), you can omit the column list:

INSERT INTO EmployeeHistory
SELECT * FROM Employees

If the destination table doesn't exist, you can create a new table and copy data into it using SELECT INTO:

SELECT * INTO EmployeeBackup
FROM Employees

The SELECT INTO statement creates a new table and copies data into it. This method is helpful for creating temporary tables or backups.

Column list in INSERT INTO SELECT

When using INSERT INTO SELECT, it's important to specify the columns carefully.

Column order matters

When providing the column list, the order of the columns must match between the INSERT and SELECT statements, even if the column names are different.

INSERT INTO EmployeeDetails
(FirstName, LastName, EmployeeID, Salary)
SELECT EmployeeID, FirstName, LastName, Salary FROM Employees

Here, the column order is different in the SELECT and INSERT statements. If you try to insert EmployeeID into the FirstName column, it will result in an error.

Specify both INSERT and SELECT column lists

Always explicitly specify the column list in both the INSERT and SELECT statements. While it’s possible to use SELECT *, it’s better to avoid surprises and explicitly list the columns.

Fill in the blanks

If the destination table has columns not present in the source table, you can fill them in by providing a default value. If the column is nullable, you can omit it from the list.

For example, if the destination table has a column Processed that doesn’t exist in the source table, you can set it to a default value of 0:

INSERT INTO EmployeeDetails
(EmployeeID, FirstName, LastName, Salary, Processed)
SELECT EmployeeID, FirstName, LastName, Salary, 0 AS Processed FROM Employees

The alias AS Processed is added for readability but isn’t required.

Generate column list from INFORMATION_SCHEMA

Manually typing the column list can be tedious, but you can easily generate it using the INFORMATION_SCHEMA.COLUMNS view.

DECLARE @columnList VARCHAR(MAX)

SELECT @columnList = COALESCE(@columnList + ', ' + COLUMN_NAME, COLUMN_NAME) 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'

SELECT @columnList

This will output the column list which you can then copy and paste.

Copying to a table with an identity column

If the destination table has an identity column, such as EmployeeID, you must specify the column list explicitly. You can't use INSERT INTO SELECT * in this case.

For example, if EmployeeBackup has an identity column EmployeeID, and you want to copy the data from Employees:

1. To include the identity column:

SET IDENTITY_INSERT EmployeeBackup ON;

INSERT INTO EmployeeBackup
(EmployeeID, FirstName, LastName, Salary)
SELECT EmployeeID, FirstName, LastName, Salary FROM Employees

SET IDENTITY_INSERT EmployeeBackup OFF;

2. To generate new identity values (exclude the identity column):

INSERT INTO EmployeeBackup
(FirstName, LastName, Salary)
SELECT FirstName, LastName, Salary FROM Employees

Create an index for the table created with SELECT INTO

When creating a table using SELECT INTO, the new table won't have primary keys or indexes. This can lead to performance issues if the table is large or frequently queried.

For example, you can add a primary key after creating the table:

SELECT * INTO EmployeeBackup FROM Employees

ALTER TABLE dbo.EmployeeBackup 
ADD CONSTRAINT PK_EmployeeBackup PRIMARY KEY CLUSTERED (EmployeeID)

You can also create indexes on other columns for performance optimization, like this:

CREATE INDEX IX_EmployeeBackup ON dbo.EmployeeBackup (Salary DESC)

This ensures that the new table has appropriate indexes for faster querying.