This should be a simply query, but its driving me crazy 1

[hoggle]

I have 3 tables(#tblMan, #tblFin, #tblAdd) all of them have the same attributes (ItemNum, totQty, ManagerNum, BasePrice)

what I want to do is first put all the records from #tblMan into #tblAdd (no problems there )

then I need to get all the items that are in #tblFin into #tblAdd, if they are not already there (from #tblMan). Pkey is ItemNum.

I've tried
====================================
insert into #tblAdd
( ItemNum,
totQty,
ManagerNum,
BasePrice)
select F.ItemNum,
F.totQty,
F.ManagerNum,
F.BasePrice
from #tblFin F, #tblMan M
where
F.ManagerNum = '0004'
and F.ItemNum not in (select ItemNum from #tblMan)


I've also tried
====================================
insert into #tblAdd
( ItemNum,
totQty,
ManagerNum,
BasePrice)
select F.ItemNum,
F.totQty,
F.ManagerNum,
F.BasePrice
from #tblFin F, #tblMan M
where
F.ManagerNum = '0004'
and F.ItemNum <> M.ItemNum


=============================================

in tblMan I have 96 records
in tblFin I have 128 (most of which are already in tblMan)
when I run the above query I get 6336 into tblAdd
at the most it would be 224 (but should be more like 150)

 

[sunila7]

Hi,

Try this query, UNION shd eliminate all the duplicates. so u shd disticnt recs from both tbl's

insert into #tblAdd
( ItemNum,
totQty,
ManagerNum,
BasePrice)
select ItemNum,
totQty,
ManagerNum,
BasePrice
from #tblFin
UNION
select ItemNum,
totQty,
ManagerNum,
BasePrice
from #tblman

Hope it helps

Sunil

 

[swampBoogie]

nsert into #tblAdd
(    ItemNum,
    totQty,
    ManagerNum,
    BasePrice)
select     F.ItemNum,
    F.totQty,
    F.ManagerNum,
    F.BasePrice
from #tblFin F
where
F.ManagerNum = '0004'
and F.ItemNum not in (select ItemNum from #tblMan)

 

[hoggle]

I spaced it for a second, I just selected distinct on the tblFin and everything works.
thanks sunila7

 

[AWithers]

you need to join the tables and create an unmatched query

something like
insert into #tblAdd
( ItemNum,
totQty,
ManagerNum,
BasePrice)
select F.ItemNum,
F.totQty,
F.ManagerNum,
F.BasePrice
from #tblFin F
left outer join #tblAdd A on
f.itemNum = A.itemnum

where
A.ManagerNum = '0004'
and A.ItemNum is null

 

[Eduofon]

Use this, just remove #tblMan from your select:
insert into #tblAdd
( ItemNum,
totQty,
ManagerNum,
BasePrice)
select F.ItemNum,
F.totQty,
F.ManagerNum,
F.BasePrice
from #tblFin F
where
F.ManagerNum = '0004'
and F.ItemNum not in (select ItemNum from #tblMan)