Author: Steven Neiland
Published:

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

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

What Do You Think?

Reader Comments

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub