Orcale to Sqlserver

Question

Orcale to Sqlserver

debbie Choudhury-370321

SSC Rookie

Points: 28
More actions
November 27, 2006 at 9:13 am

#193282

Hi,
We have a view in Oracle and it does not have any field/fields that can be indentified as unique key. Every night we need to copy all the data from that oracle view to sqlserver table (say table A). There is another table in sqlserver say B that has some structure as table A. Now I need to compare all the records between table A and table B and find the records that exists in table A but not in table B and insert into say table C.
What is the best way to do this?

Thanks,
Deb

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

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

Lowell SSC Guru Points: 323547 More actions · Answer 1

could the entire row of each data be considered unique? or would there be multiple rows with identical data?

basically, if you can't identify what makes the row unique, you can't join them against another table to compare them.....

if you know that the whole row is unique, you could use some of the functions like checksum, binary_checksum and CHECKSUM_AGG to compare rows or groups of data.

here's an example:

create table #fruit1(Fruitname varchar(30),price money)

insert into #fruit1

select 'APPLE',0.49

union

select 'ORANGE',0.55

union

select 'BANANA',0.55

create table #fruit2(Fruitname varchar(30),price money)

insert into #fruit2

select 'apple',0.49

union

select 'orange',0.55

union

select 'banana',0.55

select * from #fruit1

full outer join #fruit2 on checksum(#fruit1.Fruitname,#fruit1.price) = checksum(#fruit2.Fruitname,#fruit2.price)

select * from #fruit1

full outer join #fruit2 on binary_checksum(#fruit1.Fruitname,#fruit1.price) = binary_checksum(#fruit2.Fruitname,#fruit2.price)

results:

Fruitname	price	Fruitname	price
APPLE	.4900	apple	.4900
BANANA	.5500	banana	.5500
ORANGE	.5500	orange	.5500
Fruitname	price	Fruitname	price
APPLE	.4900	NULL	NULL
BANANA	.5500	NULL	NULL
ORANGE	.5500	NULL	NULL
NULL	NULL	apple	.4900
NULL	NULL	banana	.5500
NULL	NULL	orange	.5500

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!

debbie Choudhury-370321 SSC Rookie Points: 28 More actions · Answer 2

Thank you, Lowell.

No, there is no identical data. Let me try your advice and see if it works for me.

Can you use where clause with checksum?

Thanks,

Debbie

Lowell SSC Guru Points: 323547 More actions · Answer 3

checksum() is just a function that returns a value...i like to think of it as being like MIN or MAX functions;

you said there is nothing that makes the data unique...so how are you going to filter with a WHERE? maybe an example of the data is needed to really decide.

it's the JOIN that is critical...if there is 44 columns, you would join on the checksum of all 44 columns, for example. of course you can filter additionally with the join, so you don't really use a WHERE statement:

select * from #fruit1

full outer join #fruit2

on checksum(#fruit1.Fruitname,#fruit1.price) = checksum(#fruit2.Fruitname,#fruit2.price)

AND #fruit1.IMPORTEDDATE > '11/15/2006' --exclude old data?

AND fruit1.INVOICENUM > 456 --exclude invoices or something that doesn't match the required compare criteria

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!