How do I copy a table from one database instance to another database instance?

Tags:
Database
DBA
How do I copy a table from one database instance to another database instance?
ASKED: February 28, 2008  11:41 AM
UPDATED: February 29, 2008  1:50 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

we would need to know what type of database you are using before we can answer that with specifics. Are you using Microsoft Access, Microsoft SQL server, MySQL, PostgreSQL, Oracle, etc?

in MySQL it is possible to use the dump command to dump just a table. Research the command on <a href=”http://www.mysql.com”>mysql.com</a>

there are some easy commands in MS SQL:

SELECT * INTO NewTable FROM existingdb.dbo.existingtable;

you would need to modify it to suit your DBs. but other databases have similar commands and it all depends on what database you are using and what version it is. Give us some more info and we can help you out. Mrdenny is a DB architect and I am sure he can go into a more in-depth discussion on this issue but he will also need to know the type and version of database you are using.

With Microsoft SQL you can use Data Transformation Services (DTS) to do this for you via a wizard, just be sure and select the source and destination databases as the proper source and destination you need. With other SQL server types the method is similar, basically export it from the source and import that data into the destination table on the new database.

—————-
The SELECT INTO command shouldn’t ever be used to move data. It causes locking of system objects which can lead to major performance issues when run against large tables. More info can be found <a href=”http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1284087,00.html”>here</a>.

You’ve got a few options when it comes to importing and exporting data in Microsoft SQL Server.

In SQL 2000 and below you’ve got DTS which can easily move data from table to table. You’ve also go the import/export wizard to help you through the process if you aren’t used to using DTS.

In SQL 2005 and above DTS was replaced with SSIS. It’s a little more complex, but you’ve still got the import/export wizard to help you through the process.

If you are up to some typing in a DOS prompt, you can use BCP to import and export the data. It’s a little less forgiving because it’s a command line tool, but once you get the syntax down it’s very easy to use, and can be much faster than using DTS or SSIS.

Most every RDBMS will have some sort of import/export tool that can be used to handle this kind of work.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following