Hello Again, Next problem, I wan 3

[MarkEmerson]

Hello Again,

Next problem, I want to compare a list of codes in one table(tblA) to a list of codes in another table(tblB). Any codes that are in tblB but NOT in tblA should be written to tblC.

---------------------------
INSERT tblC
select Code
from tblA
where not exists
(select *
from tblB
where Code = tblB.Code)
order by Code
Go
----------------------------

When I execute this code it copies all records across even though there are some that shouldnt be copied. Why is this and how do I fix it?

both Code fields in tblA and tblC are varChar(7)

Thanks again, Mark

 

[mwolf00]

INSERT tblC
select Code
from tblB
where code not in
(select code
from tblA)
order by Code
Go

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[SQLSister]

INSERT tblC
select Code
from tblb left join tblA
on tblb.Code = tbla.Code
Where tbleA.Code is null

 

[swampBoogie]

You used the wrong table qualifier in the subquery.

INSERT tblC
select Code
from tblA
where not exists
    (select *
    from tblB
    where Code = tblA.Code)

 

[MarkEmerson]

Thanks all, ahh I see SwampBoogie wondered why that wasnt working - think ill go with the "is not" solution.

Is there any performance differences between th two u know of?

 

[SQLSister]

yes is not is much less efficient. Always use a join if you can for efficientcy sake.