Copying Data Between Tables Using Insert Into Select or Select Into

Sometimes it is necessary to copy data between tables in a database. Now you can do this at the application level but it is faster to do this at the database level. It is also quite easy. Here are two ways you can do this.

Copy Data Between Tables Using INSERT INTO SELECT

If you have two tables with identical structure and you simply want to clone the data from table1 to table2 then you can use INSERT INTO SELECT like this.

INSERT INTO table2 SELECT * FROM table1

The above query does a complete copy of table1 to table2. Obviously you can process and filter the select statement as long as the end result columns from table1 match the target table2.

INSERT INTO table2 SELECT * FROM table1 WHERE col1 = 'something'

Copy Specific Columns with INSERT INTO SELECT

A variation of this is when you only want to copy specific columns from one table to another. To do this just specify the target and matching source columns. Any columns in the target table that get omitted will be populated by nulls or their default value.

INSERT INTO table2 ( col1,col2,col3 ) SELECT cola,colb,colc FROM table1 WHERE cola = 'something'

Copying Data Into A New Table With SELECT INTO

The SELECT INTO method works in a similar way to the INSERT INTO SELECT method above but with the added benefit of having the capability to create a new table in the process.

SELECT col1, col2, col3 INTO newTable FROM table1

Another added benefit of this method is that the logs generated is smaller in comparison. This can make recovery of a database faster depending on your recovery method configuration.

Copying Structure Without Data Into A New Table With SELECT INTO

If you want to copy just the structure of a table to a new table, or a subset of its structure you can simply add a filter that returns no data like this.

SELECT col1, col2, col3 INTO newTable FROM table1 WHERE 1 = 2