SQL Server - Copy data from one table to another

To copy data from one table to an existing table, use INSERT INTO SELECT and specify the column list:

INSERT INTO MovieYears 
(Title, YearOfRelease)
SELECT Title, YearOfRelease FROM Movies
Code language: SQL (Structured Query Language) (sql)

If the columns are the exact same in the two tables (and there are no identity columns), you don’t need to specify the column list:

INSERT INTO MoviesHistory
SELECT * FROM Movies
Code language: SQL (Structured Query Language) (sql)

When the table doesn’t exist, use SELECT INTO, specifying which columns you want:

SELECT * INTO MoviesBackup
FROM Movies
Code language: SQL (Structured Query Language) (sql)

SELECT INTO creates the table and copies to it. This is a good way to create temp tables or backup copies of tables.

Table of Contents

Column list in INSERT INTO SELECT

There are a few things to know about the column list when using INSERT INTO SELECT.

Column order matters

When you need to specify the column list in an INSERT INTO SELECT statement, the column order matters. The column names don’t matter. SQL Server inserts columns in the exact order you specify.

Here’s an example to show that only the column order matters. Notice the select column list (YearOfRelease, Title) is not the same as the insert column list (Title, YearOfRelease):

INSERT INTO MovieYears 
(Title, YearOfRelease)
SELECT YearOfRelease, Title FROM Movies
Code language: SQL (Structured Query Language) (sql)

Because it tries to insert the Title nvarchar column (ex: ‘Office Space’) into the YearOfRelease int column, it results in a conversion error and stops the insert:

Conversion failed when converting the nvarchar value ‘Office Space’ to data type int

If you’re lucky, you’ll run into an error like this when inserting in the wrong order. In the worst case, the insertion will go through with data in the wrong columns.

Specify insert AND select column lists

If you’re specifying the insert column list, always specify the select column list as well. Technically you’re not required to, and can use SELECT *, but it’s better to explicitly specify the column list so there are no surprises.

Fill in the blanks

When the source table doesn’t have a column that’s in the destination table, fill in the blank by providing an appropriate default value for the column. If it’s nullable, you can just exclude it from the column list.

Here’s an example of providing a default of 0 for the MovieYears.Processed column:

INSERT INTO MovieYears 
(YearOfRelease, Title, Processed)
SELECT YearOfRelease, Title, 0 as Processed FROM Movies
Code language: SQL (Structured Query Language) (sql)

Note: This is adding the ‘as Processed’ column alias for readability only. It’s not needed.

Generate column list from INFORMATION_SCHEMA

Needing to type out the column list is tedious. Luckily you can generate the list. You can either use Script Table As > INSERT in SSMS and copy/paste the columns. Or you can get the columns by querying INFORMATION_SCHEMA:

DECLARE @columnList varchar(max) 

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

SELECT @columnList
Code language: SQL (Structured Query Language) (sql)

This outputs the column list, which you can then copy/paste:

Id, Title, YearOfRelease, Description, Director, BoxOfficeRevenueCode language: plaintext (plaintext)

Copying to a table with an identity column

When you want to copy to a table with an identity column, you have to specify the column list. You can’t use INSERT INTO SELECT * in this scenario.

For example, let’s say the MoviesBackup table has an identity column called MovieId, and you want to copy data from the Movies table to this table.

If you want an exact copy of the data, include the identity column in the column list and turn on IDENTITY_INSERT:

SET IDENTITY_INSERT MoviesBackup ON;

INSERT INTO MoviesBackup
(MovieId, Title, YearOfRelease, Description, Director, BoxOfficeRevenue)
SELECT MovieId, Title, YearOfRelease, Description, Director, BoxOfficeRevenue
FROM Movies

SET IDENTITY_INSERT MoviesBackup OFF;
Code language: SQL (Structured Query Language) (sql)

If you’re OK with it generating a new identity value for the copied data, then exclude the identity column from the column list:

INSERT INTO MoviesBackup
(Title, YearOfRelease, Description, Director, BoxOfficeRevenue)
SELECT Title, YearOfRelease, Description, Director, BoxOfficeRevenue
FROM Movies
Code language: SQL (Structured Query Language) (sql)

Create an index for the table created with SELECT INTO

When you create a table with SELECT INTO, the table will have no primary key or indexes. Depending on how you’re going to use the new table, insufficient indexes can lead to performance problems.

In most cases, you’ll at least want a primary key. You can add one with an ALTER TABLE statement after you’ve used SELECT INTO to create the table:

SELECT * INTO MoviesBackup FROM Movies

ALTER TABLE dbo.MoviesBackup ADD CONSTRAINT PK_MoviesBackup PRIMARY KEY CLUSTERED (MovieId)
Code language: SQL (Structured Query Language) (sql)

You can create appropriate indexes by using a CREATE INDEX statement. For example, the following is creating a non-unique index on the YearOfRelease column:

CREATE INDEX [IX_MoviesBackup] ON [dbo].[MoviesBackup] ([YearOfRelease] ASC)
Code language: SQL (Structured Query Language) (sql)

SQL Server – Copy data from one table to another