I have a sql server 2005 stanadard edition.
I have a series of tables staging and lve table with around 5-6 million records in each.
The staging tables represent data improted weekly via SSIS now my problem is that i want to migrate / merge the 5 million records from the staging table to the 5million records in the live table.
I've tried doing this via ssis and simple stored procedures but it just hangs the server and kills the server /db,
I've had a consultant to come in and opimise these tables but were still no were near solving this problem. However the consultant has since advised that we use sql replication to do this instead, as its designed for this type of job, and will avoid any server hangs.
I not familiar with this myself and as such wanted to get a 2nd opinion and ask is this advisable and how would this work?