How to Copy data from one table to another in SQL
By FoxLearn 3/7/2025 7:47:13 AM 184
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.
- How to Query JSON in SQL Server
- How to modify JSON in SQL Server
- How to set time to 00:00:00 with GETDATE() in SQL
- How to find all the dependencies of a table in SQL Server
- How to Find Objects Referencing a Table in SQL Server
- Case sensitivity in SQL Server
- How to Convert varchar to uniqueidentifier in SQL Server
- How to use GROUP BY in SQL