Juixe Techknow

Most applications require a database of some sorts, and when working with a database you most likely will have a production, development, and test database to work with in addition to backups. A common problem I found is that sometimes I want to move rows of data for a particular database table between the different databases I work with. For example, I found I need to copy a subset of data from a table from the production database to the test database so that I had good data to test with. A typical approach is to create a backup from the production database and restore it as a test database, but if you just want to copy a subset of rows from a particular table that approach is overkill.

In SQL Server, you can use a insert statement to insert new rows in a database where you select and filter the data from a different database. This is also true for other database vendors but the SQL might be slightly different.

To further illustrate this, imagine I have a database table blog_posts in a production database called BlogProduction and a test database is called BlogTest and they both reside on the same SQL Server instance. From SQL Server Management Studio or SQLCMD or from where ever you run your SQL statement you can run the following statement.

INSERT INTO blog_posts
SELECT * FROM BlogProduction.dbo.blog_posts

If you run the above statement from the perspective of the BlogTest database, you are will insert all the records from the blog_posts table in the BlogProduction database into the blog_posts table in BlogTest. The above example, assumes the database owner is dbo. It is not required to name the database owner and the following does the same.

INSERT INTO blog_posts
SELECT * FROM BlogProduction..blog_posts

You can also add a where clause to the select statement to filter out certain data to insert to your current database.

INSERT INTO blog_posts
SELECT * FROM BlogProduction..blog_posts
WHERE id > 100

In this way, you can move data from different database tables from different databases.