Currently I have an SSIS job that runs to move data from one sql 20008 machine to another. The job moves about 2 million records from about 6 tables. That takes about 5-10 minutes depending on the server load and that is fine. Because the data is moved into temp tables so nothing is affected besides the strain on the server.
But my problem becomes when I now want to merge that data with their respective live tables. That can take about 15 minutes during which the tables are emptied and then repopulated. What I am wondering is what is the most efficient way to move that data between the tables.
Currently here is how it goes:
drop tables
rebuild tables with indexes and constraints
insert into select to move the data
then run any calculations that are needed
run following command to rebuild all the indexes after the data has been moved:
sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
I feel like there should be a better way so that there is minimal down time to the users. One thought I had was to create a second set of tables and then just rename those once they are ready but I am not sure if that is the best way either.
I have also just read about the merge command which might be better as I don't have to drop the tables and repopulate which means all the data would stay available but it is hard to know if the records change without looking at nearly all the columns.
I would appreciate any help.
After considering the option for the view with different underlying tables I decided against to avoid the complexity and confusion that it might introduce. I looked into partitioning but since I do not have much control on the source machine it didn't seem like the appropriate solution. So in the end I just decided to use the SQL MERGE statement and use BINARY_CHECKSUM to compare rows and determine differences or not. Thhough I am not having some issues with the locking with that. But I have opened another question for that.