Feb 1 2011

Copy Data Between Two Database Tables

There are many situations when you need to copy data from one database table to another one. You may want to migrate data from one database table to a new table, sometimes you need to do so to copy data to a backup table, other times to bulk load data from from different databases. To copy date between tables in a SQL Server database you can execute the following.

INSERT INTO Note (Id, Name)
SELECT Id, Name FROM Notes
WHERE name IS NOT NULL

The above state will copy date from a database table Notes to a new table Note where some condition is meet.

A typical database server instance hosts multiple databases, such as production and several test databases. You can copy data not only between tables within a database, but between databases within a database server instance. To copy between databases you need to use the fully qualified database name. For example, the following SQL snippet copies data from a tables called Notes in a database called TestDB.

INSERT INTO Note (Id, Name)
SELECT Id, Name FROM TestDB.dbo.Notes
WHERE name IS NOT NULL