Copy data from one database to another

Question

Post reply

Copy data from one database to another

Lionel2007

Old Hand

Points: 366
More actions
October 3, 2007 at 1:36 pm

#375131

Hi have two exact database,
How can I copy data from one table from database x to another table in database y that have the exact colums.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Assuming there are no identity columns and that both DBs are on the same server, this should work:

INSERT INTO destinationDB..TableName

SELECT *

FROM sourceDB..TableName

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 2

Or you can use DTS or SSIS wizard to copy data from one database to another

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 3

If the databases are in the same instance, i recommend John Rowan's post

"-=Still Learning=-"

Lester Policarpio

Lionel2007 Old Hand Points: 366 More actions · Answer 4

Lionel2007

Old Hand

Points: 366

October 4, 2007 at 6:07 am

#739006

what is DTS or SSIS wizard?

Lionel2007 Old Hand Points: 366 More actions · Answer 5

Another question:

How do I copy a table from one database to another on the same server?

Without losing the dependency and keys?

JustABob SSC Eights! Points: 804 More actions · Answer 6

Not an expert on this, but here goes -

Open QA and set it to the destination DB.

Run a series of queries

Select *

Into Table_A

from sourceDB.dbo.Table_A

Select *

Into Table_B

from sourceDB.dbo.Table_B

.....

Select *

Into Table_Z

from sourceDB.dbo.Table_Z

This will create the table and insert the data from the source database over to the new one. That means that all the keys, rows, values should be moved over.

I prefer the route of generating the script to create the tables and have that make them - that way you are sure that the columns are exactly the same, then insert the data into the tables. It does take a bit longer to do though.

Either way should work fine. If this is a clean database, just give it a try and see what happens if you are worried about dependencies. The worst case would be you have to delete the tables and start over.

John Rowan SSC Guru Points: 56440 More actions · Answer 7

Lionel2007,

It looks like your scope is creeping a bit here. Can you post the table DDL for the tables you'd like to copy over. If there are data dependencies in other tables that need to be moved also, include the DDL please.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Randy Dray SSC Enthusiast Points: 158 More actions · Answer 8

Hello, I manage all my data moves with redgate SQL Data Compare tool. It will compare and move data for you. For just on or two tables I would use the posted INSERT INTO with a SELECT. Moving lots of data in lots of tables proves challenging. The good thing about the data compare tool is you can , without question, know all your data was moved EXACTLY as you asked because the data compare tool will compare both database after it has moved it and you will see no differences, If you do , you can look into it.

Thanks,