Solved

Using SQL Replicataion to merge data in two tables

Posted on 2011-03-22
5
239 Views
Last Modified: 2012-05-11
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?

Thanks
Webbo.
0
Comment
Question by:Webbo_1980
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 35197445
Different types of replication exist but mostly applied at table level between databases or servers.

Here are 2 high level article on different types of replication in SQL Server.

http://databases.about.com/cs/sqlserver/a/aa041303a.htm
http://msdn.microsoft.com/en-us/library/ms151198.aspx

How do I... Configure transactional replication between two SQL Server 2005 systems?
http://www.techrepublic.com/blog/howdoi/how-do-i-configure-transactional-replication-between-two-sql-server-2005-systems/123

SQL Server 2005 - Configuring Replication
http://www.exforsys.com/tutorials/sql-server-2005/sql-server-configuring-replication.html

Cry How to... SQL Server Replication
http://www.cryer.co.uk/brian/sqlserver/howtoreplication.htm

One good idea will be to setup bcp export and import to merge the 5 million records from the staging table to the 5million records in the live table. Even you can try bulk insert. The data will be contained in a non delimited/delimited text/csv/excel file. You can automate/schedule these activities using windows scheduler.  
0
 

Author Comment

by:Webbo_1980
ID: 35197514
Thank you for the reply, but non of the links above use replication in the way suggested to me i.e. using it to merge data found in two tables on the same db ?


I've already imported this data from a csv files and imported all the tables into the staging table, its the merge which is taking the time.

Is this advisable? and is this the best way?

0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 35204406
"i want to migrate / merge the 5 million records from the staging table to the 5million records in the live table."

The best way to do this should be using bcp or bulk insert which is automated via task scheduling. It is simple to implement and pretty fast unlike "I've tried doing this via ssis and simple stored procedures but it just hangs the server and kills the server /db, ".
0
 
LVL 10

Accepted Solution

by:
TAB8 earned 500 total points
ID: 35204918
Replication is not your solution ..   ONe table will have to be considered the publisher and one the subscriber ...   to set up replication all the data from the publisher overwrites all the data in the subscriber ...   it will not merge the data together !!!

What you need to do in create a new table with no constraints ....  bcp both sets of data into the new table then select the unique data from that table into your prod table ..

Or you can use a database comparison tool "like the one form redgate" this will compare two sets of data and can update tables so you have one unique set of data
0
 

Author Closing Comment

by:Webbo_1980
ID: 35361534
Thank you everyone but despite weeks of trying ive given up come to the conclusion replication want meant tobe used this way
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question