Copying/Moving data from one database to another in SQL Server 2000.

[kel1981b]

I am not DBA Person but need to do that :-(. How can I Copy/Move data from table in Production Data base to the same table in Test Database? Is there some tool/option in SQL Server 2000. Need help ASAP. Any help will be highly appritiated.

 

[SQLSister]

YOu can use DTS or the import Export wizard or write a stored procedure to do this depending on your needs. If you are new to this the Wizard is probably the simplest way. Right click on the tablename in Enterprise Manager and select All tasks and then Import or export data and then just follow along with the wizard. You might read about this subject in Books Online for mored etails on the process.

 

[kel1981b]

Thanks for your quick reply. I tried to use DTS and had some problems. For example. I need to migrate data to MyTable. This table realted with TheirTable. So I must update both tables. I could not find DTS options to do that. I am sure that I missed something but I do not know what.

 

[SQLSister]

Generally that is a problem of the order in which you import the tables. Import the main table first, then the related table and the problem should go away, unless you are letting the system create it's own identity values inwhich case they may not be the same as the ones in the production tables and thus the related records will not match up. Usually if I am importing data from production back to development or testing, I will enable the identity insert on so I get the same identity values in all the tables that production has, making it less of a problem to import related tables. That is an option even using the wizard if you look at the actaul transformation.