Transfer of Data between 2 SQL Server Databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Transfer of Data between 2 SQL Server Databases

Author

Topic

mepancha
Starting Member

5 Posts

Posted - 2005-03-21 : 08:24:48

Hi,
We would like to transfer data from one SQL Server database to another, both the database are sitting in different servers and are of the same version SQL Server 2000. Please let me know the method to transfer data, should I be using DTS or is there any other simple method that can be used.In Oracle this could be achived using Database link, wanting to know if there was something equivalent to that in SQL Server. We are looking of transferring only 10,000 records one time and on a daily basis we would like to transfer about 100 records.

Thanks in advance.

Kristen
Test

22859 Posts

Posted - 2005-03-21 : 15:02:49

You could Backup & Restore the database. That will copy your database structure, procedures as well as the data. There are some issues with transferring logins this way.

You could transfer just the data using DTS. For this to work either the database structure must exist at the remote end, or you will be happy with a "basic" table structure (no FKeys, indexes, etc.) The issues with this are to do with what you would expect to happen to any existing data. If you have FKs on the target tables you cannot just "evaporate" any pre-existing data.

Home brewed script - e.g. Delete data from table TargetA, which does no existing in table SourceA. Do this in "reverse FK order". Then UPDATE pre-existing, but different, rows (in FK order). Then insert new rows into TargetA from SourceA (in FK order)

To do this you would need to set up a Linked Server - basically allowing the Remote server to be visible from the source server (or the other way round)

Export/Import - use BCP to transfer data in bulk, via files - either as Native-Format files, CSV, XML or some other format. Import at the far end - same issues above of FKs and DELETE / UPDATE$ / INSERT process.

Kristen