Copy data from one database to another.

Question

I need to copy the data from one database to another only if the table schema is same... a program to do that is possible but its too much work any quick short method?

Answer 1

you can use Bulk Insert. Or Import and export in SQL Server

Answer 2

If the schema is same then take a database dump from one database and restore in the other database.

Other option is to export the data in file or excel and then import them into the other database.

Otherway is create a dblink in the second database and execute a query like insert into ... (select from ...)

Answer 3

You can use BCP utility for MSSQL Server 2000, or SSIS (SQL Server Integration Services) from MSSQL 2005 or 2008.

Answer 4

A crude work-around: In SQL Server 2005 and up, you could write a direct INSERT...SELECT statement (using linked servers if they're on different SQL instances), and then wrap it in a TRY/CATCH block. If it copies, good, if it fails, the error gets caught and managed.

Further issues will crop up, such as if the target table has an extra column that's nullable or has a default, but its a start.

-- Adding this in response to your comment --

How to write "INSERT...SELECT" statements for all your tables in under a minute:

First, each command looks like so:

INSERT TargetDB.dbo.xxx select * from xxx

This does use SELECT *, but since you're looking for exactly matching tables that shouldn't be a problem. Next, to write one of these for each user-defined table in the currently selected database, run this:

SELECT 'INSERT TargetDB.dbo.' + name + ' select * from ' + name
 from sys.tables

Replace "TargetDB" with the target database, run, cut and paste the results, and then work in the TRY/CATCH block.