Compare Two Tables which are missing data

[orreymalcolm]

Hello,

I am currently working on database where I need to compare 2 tables with each other and see which data in table 1 is not already in table 2. Table2 has a mix of data sorted by an ID number and table 1 is sorted by a another type of ID code.

The problem lies here. Table 2 needs to have everything table 1 has inside of it, but many times (approx 500), but because there is already some of table 1 data in table 2, I can't have the data overwrite itself.

Is there a way to do this? I've been looking at using the UNION ALL statement in conjunction with INSERT INTO and a few others. I hope there is a solution to this.

Thanks in advance!

 

[Crowley16]

you can use a left join or a not in to identify records in one table but missing from another...

e.g.

select
a.fld,
b.fld
from
tableA A left join
tableB B on a.fld = b.fld

the missing records will be null in b.fld

or

select
fld
from
tableA
where
fld not in (
select fld
from tableB
)


--------------------
Procrastinate Now!

 

[orreymalcolm]

Hey Crowley!

thanks for the response, it seems like it will work, I won't be able to tell until i add my append. I wanted your opinion on my code to append.

currently it will be something like this

INSERT INTO <members-and-contracts> (<agreementNUM>, <memberNUM>)
SELECT <agreementNUM>, <memberNUM>
FROM <contract-list>
ALTER TABLE <members-and-contracts>
ADD CONSTRAINT CheckForDuplicate
CHECK (<members-and-contracts>.<agreementNUM) <> <contracts-list>.<agreementNUM>)

something like that anyway....I can tell it's not efficient, but I'm doing a million things at a time right now lol, so any help would put my brain at ease.

 

[fneily]

You can also see:
thread701-1053292