SQL Insert if Values doesn't exist

Question

So I need to insert values into a table, but if those values exist I want my script to not insert anything. I have pretty much gotten it solved, but I am stuck at the very end with my insert statement.

DECLARE @billableitemID int;
set @billableitemid = (select billableitemID from dbo.treatment where 
@billableitemid=treatment.id)
set @billableitemID = 256



CREATE TABLE #MembershipBenefitItem
    ( BenefitID int ,
      BillableItemTypeID int ,
      BillableItemID  int
    )

INSERT INTO #MembershipBenefitItem
    ( BenefitID ,
      BillableItemTypeID ,
      BillableItemID 
    )

 VALUES  (23450,1,@billableitemid),
(57256,1,@billableitemid)

select m.* from #MembershipBenefitItem  AS M
left join membershipbenefititem AS M1 on m.billableitemid= m1.billableitemid 
and m.billableitemtypeid= m1.billableitemtypeid 
and m.benefitid= m1.benefitID
and M1.billableitemID=@billableitemID and M1.billableitemtypeID=1 and 
M1.benefitID IN(23450,57256)  
where M1.ID is null

INSERT INTO MembershipBenefitItem
    ( BenefitID,
      BillableItemTypeID,
      BillableItemID 
    )

 select m.BenefitID, m.BillableItemTypeID, m.BillableItemID from 
#MembershipBenefitItem  AS M
left join membershipbenefititem AS M1 on m.billableitemid= m1.billableitemid 
 and m.billableitemtypeid= m1.billableitemtypeid 
 and m.benefitid= m1.benefitID
and M1.billableitemID=@billableitemID and M1.billableitemtypeID=1 and 
M1.benefitID IN(23450,57256) 
where M1.ID is null



DROP TABLE #MembershipBenefitItem

Another way to do such a thing would be the Merge statement. Just a thought. Typically though this is used for If exists Update else Insert — Holmes IV
– Holmes IV, Commented Jun 1, 2017 at 20:08

haku · Accepted Answer · 2017-06-01 20:18:38Z

1

This looks like a good case to use Merge statement.

https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql

It would look something like:

MERGE MembershipBenefitItem AS trg
USING #MembershipBenefitItem AS src 
ON src.BillableItemID = trg.BillableItemID --and other join conditions
WHEN NOT MATCHED BY TARGET THEN 
INSERT (col1, col2, col3) -- this will be inserted into trg table
VALUES (src.val1, src.val2, src.val3)

edited Jun 1, 2017 at 20:18

answered Jun 1, 2017 at 20:08

haku

4,5649 gold badges47 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Insert if Values doesn't exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related