Copying some tables from one database to another

Question

Copying some tables from one database to another

webskater

SSCarpal Tunnel

Points: 4214
More actions
March 29, 2010 at 3:32 am

#231263

I have 3 databases used for development, user acceptance testing and live.
The database stores the data used by 4 different departments in an organisation. One of the departments have asked for their data in the live database to be copied to the user acceptance testing database - so that the data is more meaningful and easier to test. (The UAT database inevitably fills with junk and nonsense).
There's about 20 tables that need to be copied. They are all interdependent in that they are set up with primary keys, foreign keys and referential integrity enforced.
How can I copy those tables from the live database to the UAT database and maintain the data integrity so that it all hangs together properly in the UAT database?
Thanks for any help.

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

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

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

What is the tool that u are using to copy the tables? u can disable the constraints, copy the data and enable the constraint.

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

sku370870 (3/29/2010)
I have 3 databases used for development, user acceptance testing and live.
The database stores the data used by 4 different departments in an organisation. One of the departments have asked for their data in the live database to be copied to the user acceptance testing database - so that the data is more meaningful and easier to test. (The UAT database inevitably fills with junk and nonsense).
There's about 20 tables that need to be copied. They are all interdependent in that they are set up with primary keys, foreign keys and referential integrity enforced.
How can I copy those tables from the live database to the UAT database and maintain the data integrity so that it all hangs together properly in the UAT database?
Thanks for any help.

What is table size ?

Is this for one time purpose?

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 3

Thanks for the replies. I don't have a tool to do the copying - this is one of the things I need to find out - what is the best way to copy the data from one database to the other.

How do you measure table size?

The table that holds the main data (that many of the other tables relate to) has just under a thousand rows in it. It has 12 columns in it.

The biggest of the other tables has 4000 rows - 9 columns.

It is for a one off purpose.

vidya_pande SSCertifiable Points: 5275 More actions · Answer 4

Use import/export wizard.

Also you can save it as a SSIS package and re-use it.

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 5

vidya_pande (3/29/2010)
Use import/export wizard.
Also you can save it as a SSIS package and re-use it.

Thanks for your reply. The tables already exist in the UAT database. When I import them from teh live database - will it overwrite the data in the tables and maintain the data relationships? i.e. somehow maintain the primary and foreign keys.

webskater SSCarpal Tunnel Points: 4214 More actions · Answer 6

Hi, sorry to bump this ... will using the Import / Export wizard maintain relationships? I don't think it will - so can anyone tell me the best/easiest way to import a load of tables (with data) from one database to another whilst maintaining referential integrity.

Thanks for any help.