Transferring Data between two SQL Server 2000 Repository.

Question

I need to Extract data from a SQL repository Process It and then save it in 2nd Repository. Nature of data:

Transfer selected users data, though the tables are similar in both repository but not same, and some amount of processing needs to be done on the data to be transferred.

The volume data is very large 5,00,000.

What technique should be used to transfer the data: Use Stored procedure through Link Server to transfer data. Use DTS - data transfer service (Since We have SQL server 2000 we can’t use SSIS). Use C# to extract data from repository A, Process it and call an SP to insert data in repository B.

I am not sure which way to go, I would like to process the data in optimal and robust manner.

Any suggestion will be greatly appreciated.

Thanks in Advance.

Answer 1

It's likely that it would be fastest to park everything in a bulk data file and use BCP.exe or BULK INSERT. DTS would probably be the second fastest method.

Answer 2

DTS would be my choice for this. The GUI interface makes setting up a step by step process pretty simple, and I like the error/exception handling you can do at each step. I've used DTS to move around 15 million rows or more quite frequently, so the volume you have here shouldn't be much of an issue.

Extra points if you can get the DTS package to page a co-worker every time it succeeds in running :)

and an edit to add...I would avoid using a linked server with a Stored Proc for larger data volumes if you can...I've hit performance issues on that in the past

Answer 3

Thanks for the reply I also thought as you all said that DTS would be fastest

But we have a constraint which i thought would limit us from using DTS the problem is that we have 3 levels of hierarchy in tables (foreign keys levels) and the destination database would generate auto identity values for each table in hierarchy so my main concern is that if I will be able to maintain the relations with newly generated identity values if I use DTS

I am new to DTS concept but I have strong knowledge of store procedures and T-SQL do you think maintaining foreign key hierarchy will be an issue with DTS

Answer 4

A reply thread in answers ;)

Think of DTS packages as a string of 'events' (I'm not sure if thats the right term...how about a series of steps?)...using success/failure lines, it actually becomes a decently readable flowchart. Each step can be several different things, including executing stored procs and t-sql statements. Your DTS package can contain a mass import to a temp table, then the next step(s) initiate storedprocs to process it and eventually put it in the destination tables.