Merge between two tables , on two different databases.

Question

Merge between two tables , on two different databases.

Learner44

SSCarpal Tunnel

Points: 4466
More actions
May 30, 2013 at 1:51 pm

#273342

Hi friends,
I have following issue:
I have one table called customer in Cust database in SQL SERVER 2008R2, which is my destination table.
and Other table called customer(same name as above and also same fields) on the oracle server, which is my source table.
I have access of read only in the table which is on the oracle server under the LINKED SERVER folder in SQL SERVER 2008R2.
now I am confuse how can I do the incremental ETL,(insert , update and delete) between source and destination table either via Stored procudre or SSIS package.
Please help me.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if this was just an insert form the source, you'd ideally want to find a way to find the max(id/createddate) from the target, and get records that are greater than that from the source, to reduce the overhead of # of records copied.

since you are talking inserts, update and deletes as well, then my question back to you is:

why bother?

why not truncate the table and reinsert the whole thing via bcp/bulk insert?

a MERGE is going to require ALL the rows be copied from both the target and the source, and then the MERGE performed.

If the objective is to get teh Target to match teh source, why not just do it as a drop and recreate?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 2

Hi Lowel,

I understand your point,bu the thing is I can not delete/drop/truncate the destination.

there thousands of records, my source table is updated every day and i have to update destination table every week.

so i want to develope either stored procedure for that or SSIS package to perform incremental ETL, now the question is how I can do that, because they both are on different servers.

Thanks.

Lowell SSC Guru Points: 323518 More actions · Answer 3

i have to update destination table every week.

weekly updates screams drop and recreate to me; if you needed any changes/updates that were mere seconds old, it might be a different story.

you can use the MERGE statement

against a linked server I think, but it will end up copying every row from the Oracle server into tempdb,and then doing the merge.

an SSIS package you develop could also do it,a s you already identified.

unless you have an indicator on the oracle server, like a LastModifiedDate, which you could compare against your current snapshot, it's going to involve every row, from what I think so far.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 4

yes, i have an indicator column on oracle server table which states that "LAST WRITTEN".

for me it will be really good if i develop ssis package on first stage, or I need to go for stored procdure according to my task requirement.

I need your help..if you can provide me any example reagrding this.

I am following this article.

http://www.sqlservercentral.com/articles/EDW/77100/

for making the stored procdure.

thanks.