CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ENGR024
ENGR024

Reputation: 317

Sql join and insert if not exists

I have a table called Itemlookup and I want to insert values from two tables. However, I only want to insert it if Itemlookup doesn't have these values. I can insert into the table but if I execute it again, it inserts again. I want to avoid do that.

I have tried using the If not exists, but am not getting any luck. Any help is most appreciated. 

Insert into Itemlookup (ItemNumber, Cases, Shift, [TimeStamp])

Select a.ItemNumber, b.CaseCount, a.TimeStamp 
from ItemsProduced a innerjoin     
       MasterItemList b on a.ItemNumber=b.ItemNumber

Upvotes: 2

Views: 6347

Answers (3)

huMpty duMpty
huMpty duMpty

Reputation: 14460

Insert into Itemlookup (ItemNumber, Cases, Shift, [TimeStamp])
Select a.ItemNumber, b.CaseCount,ColumnName, a.TimeStamp 
from ItemsProduced a innerjoin     
       MasterItemList b on a.ItemNumber=b.ItemNumber
where a.ItemNumber NOt In (Select T.ItemNumber From Itemlookup T)

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31775

If you're interested, here is the correct way to use NOT EXISTS (also removing "Shift" as juergen did):

Insert into Itemlookup (ItemNumber, Cases, [TimeStamp])
Select a.ItemNumber, b.CaseCount, a.TimeStamp 
from ItemsProduced a 
inner join MasterItemList b on a.ItemNumber = b.ItemNumber
WHERE NOT EXISTS(
  SELECT * FROM Itemlookup 
  WHERE ItemNumber=a.ItemNumber
  AND Cases=b.CaseCount
  AND [TimeStamp]=a.TimeStamp
)

Upvotes: 3

juergen d
juergen d

Reputation: 204746

Try a left join

Insert into Itemlookup (ItemNumber, Cases, [TimeStamp])
Select a.ItemNumber, b.CaseCount, a.TimeStamp 
from ItemsProduced a 
inner join MasterItemList b on a.ItemNumber = b.ItemNumber
left join Itemlookup i on i.ItemNumber = a.ItemNumber
                      and i.Cases= b.CaseCount
                      and i.TimeStamp = a.TimeStamp 
where i.ItemNumber is null

BTW your insert contains 4 columns and your select only 3. I removed Shift for consistency.

Upvotes: 7

Related Questions