copy data between 2005 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)

copy data between 2005 databases

Author

Topic

jstranger
Starting Member

11 Posts

Posted - 2006-04-23 : 03:37:16

Would be grateful for suggestions on the easiest way of achieving the following.

I have a database on a remote shared hosted platform that I need to be able to update from selected tables in two local databases on a regular basis (e.g. weekly). For each selected table, I want to copy all rows, deleting any existing rows.

I have tried the Import Data Wizard in SQL Server Management Studio which I finally got to work but only after removing and subsequently recreating foreign key constraints, etc. Presumably I can bundle all of this into a package somehow, but is there really no easier way of doing what must be a fairly common requirement? Would it be simpler to create an admin web page using the SQLBulkCopy class?

Jon Stranger

Kristen
Test

22859 Posts

Posted - 2006-04-23 : 03:49:48

"is there really no easier way of doing what must be a fairly common requirement"

I expect DBAs will tell you that there are lots of ways ... but it depends on the circumstances!

1) Can you create a Linked Server between the two databases

Then just do

INSERT INTO RemoteServer.RemoteDatabase.dbo.RemoteTable
SELECT * FROM LocalDatabase.dbo.LocalTable

Or maybe you will have to use an OPENQUERY equivalent for speed/security/some-other-buggerification reason!

Do the Parent tables first, then the Child tables (if there is an issue with FKs)

Set up a schedule in SQL Server to run the task at a given time

2) Is the number of rows big?

Probably best to use BCP then. Set up a BCP export (native file format) on the Local machine. Transfer the files to the remote machine and import using BCP.

Make sure you export ordered by the Clustered Index keys on the Remote machine, and use the appropriate hint to alert the import to the fact the file is pre-ordered.

3) Servers cannot see each other at a file-share level?

Proceed as (2) but use FTP to transfer the files. Probably needs a DOS based BATCH file, or similar, to do the deed

4) Set up a crappy, fragile DTS solution. That's what it was in SQL 2000, its called something like SSIS in SQL2k5, but I doubt its any less crappy!

5) Then you have to worry about how to clear down the Remote database tables - if necessary.

If there are no FKs, and you can clear the whole table, then use TRUNCATE TABLE to minimise logging.

Otherwise use DELETE - delete the children tables first, then the parents.

If the logging cost of this will be too great consider some other policy:

o DELETE in a loop (i.e. "reasonable number of rows" deleted per iteration) to reduce the log impact.
o Or turn off logging whilst doing the clear-down - not suitable for 24/7 database that might need point-in-time recovery.
o Or DROP the FK constraints, truncate the tables, then reinstate the FKs - bit dicey if the structure of the FKs might change in the future and that change is not added to this script!

Kristen

jstranger
Starting Member

11 Posts

Posted - 2006-04-23 : 07:44:07

Kristen

Many thanks for the comprehensive reply. In the end I decided to try out the SqlBulkCopy class since that looked quite easy and meant that I could create a very user-friendly UI and have maximum control. But I still had to first delete existing rows in the destination table - which meant that I also had to create and execute stored procedures to remove and restore foreign key constraints. And I also had to find the copy option that let me keep identity column values from the source table. But it eventually worked and seems to be pretty quick.

Jon Stranger

Kristen
Test

22859 Posts

Posted - 2006-04-23 : 08:40:51

Glad you got it going! (You should be able to use the IncludeIdentityValues property of the BulkCopy object to retain existing IDENTITY values)

"which meant that I also had to create and execute stored procedures to remove and restore foreign key constraints"

That's a little bit worring - in that it implies that related data may have existed, and be dependant, elsewhere.

If you:

Delete Children first, then Parents
... and then ...
Insert Parents first, then Children

you ought to be able to leave the FKs in place.

(But not if you want to use TRUNCATE TABLE)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources