January 22, 2008 at 3:05 am
I am fairly new to SQL Server 2000 and have been asked to take some of the data from 4 tables and transfer automatically twice daily to 1 table to be used for reporting purposes. It is to go to a staging table first (whatever this is). There are about 12,000 rows of data to txfer. What would be the best way to achieve this? Would it not be better to just use a view on the original tables as the data in the reporting table is unlikely to be up to date after txfer. Would it be worth upgrading to 2005?
January 22, 2008 at 5:44 am
You could use a view to combine the data from the four tables into a single select statement. 12,000 rows, depending on how much data is in them, is not very much data to move around, so this should be easy. I'm not sure why you'd even need a staging table unless you have to do multiple inserts to put the data together prior to some sort of clean-up as you move it to its final destination. Double check the logic on this part of the process.
Deciding whether or not to move to 2005 is a totally different issue. But, if it will help, SSIS, which you'd most likely use to migrate data between systems, is a much better tool than DTS.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply