Check if record exists while inserting using User defined table type sql server

Question

I am trying to bulk insert the records in sql server. I am using User Defined Table type to pass the collection of records from my .net application. Please take a look at the insert query below.

INSERT INTO MachineItems([Name],[Price],[Quantity],[ItemGroupID],[SubGroup] ,[IsDefault],
    [IsRemovable],[MachineTypeID],[ItemType],[CreatedBy],[CreatedOn] )

    SELECT mi.Name
    ,mi.Price
    ,mi.Quantity
    ,(SELECT ID from ItemGroups WHERE NAME=mi.ItemGroup) as ID
    ,mi.SubGroup,
    CASE 
      WHEN mi.IsDefault ='Yes' THEN 1 
      WHEN mi.IsDefault ='No' THEN 0 
      WHEN mi.IsDefault IS NULL THEN 0
     END ,
     CASE 
      WHEN mi.IsRemovable ='Yes' THEN 1 
      WHEN mi.IsRemovable ='No' THEN 0 
     END ,
    (SELECT ID from MachineTypes WHERE Name=mi.MachineType),
    (SELECT ID from MachineItemTypes WHERE Name=mi.ItemType),
    mi.CreatedBy 
    ,mi.CreatedOn       
    FROM @MachineItems mi

What i want to do is put the check before inserting the records , Whether record with [MachineTypeID] and [Name] already exists in table or not. If it does not exists then insert Eles Update the record.

How can i do that with User Defined Table Type ?

Answer 1

1. You should use the MERGE command rather than a straight insert. What you are wanting to do is not really specific to User-Defined Table Types.

2. It would be better / more efficient if you joined the 3 subtables rather than having subqueries for columns which will execute per-row.

Example:

MERGE MachineItems AS Target
USING (SELECT mi.Name,
              mi.Price,
              mi.Quantity,
              ig.ID, -- ItemGroupID
              mi.SubGroup,
              CASE 
                 WHEN mi.IsDefault ='Yes' THEN 1 
                 WHEN mi.IsDefault ='No' THEN 0 
                 WHEN mi.IsDefault IS NULL THEN 0
              END, -- IsDefault
              CASE 
                 WHEN mi.IsRemovable ='Yes' THEN 1 
                 WHEN mi.IsRemovable ='No' THEN 0 
              END, -- IsRemovable
              mt.ID, -- MachineTypeID
              mit.ID, -- ItemType
              mi.CreatedBy,
              mi.CreatedOn
       FROM   @MachineItems mi
       INNER JOIN ItemGroups ig
               ON ig.[Name] = mi.ItemGroup
       INNER JOIN MachineTypes mt
               ON mt.[Name] = mi.MachineType
       INNER JOIN MachineItemTypes mit
               ON mit.[Name] = mi.ItemType) AS Source (
                   [Name],[Price],[Quantity],[ItemGroupID],[SubGroup],[IsDefault],
                   [IsRemovable],[MachineTypeID],[ItemType],[CreatedBy],[CreatedOn])
ON (
         Target.[MachineTypeID] = Source.[MachineTypeID]
    AND  Target.[Name] = Source.[Name]
    )
WHEN MATCHED THEN 
     UPDATE SET Price = Source.Price,
                Quantity = Source.Quantity,
                ItemGroupID = Source.ItemGroupID,
                SubGroup = Source.SubGroup,
                IsDefault = Source.IsDefault,
                IsRemovable = Source.IsRemovable,
                MachineTypeID = Source.MachineTypeID,
                ItemType = Source.ItemType,
                CreatedBy = Source.CreatedBy,
                CreatedOn = Source.CreatedOn
WHEN NOT MATCHED BY TARGET THEN
     INSERT ([Name],[Price],[Quantity],[ItemGroupID],[SubGroup] ,[IsDefault],
             [IsRemovable],[MachineTypeID],[ItemType],[CreatedBy],[CreatedOn])
     VALUES (Source.[Name], Source.[Price], Source.[Quantity], Source.[ItemGroupID],
             Source.[SubGroup], Source.[IsDefault], Source.[IsRemovable],
             Source.[MachineTypeID], Source.[ItemType], Source.[CreatedBy],
             Source.[CreatedOn]);

Answer 2

You can Use Merge Here

Using Merge

1. You can Insert if Not Exists
2. You can Delete if Already Exists
3. You can Update if Already Exists

---

MERGE MachineItems
USING @MachineItems ON MachineItems.id = @MachineItems.id
and [email protected]
WHEN NOT MATCHED THEN
INSERT INTO MachineItems([Name],[Price],[Quantity],[ItemGroupID],[SubGroup]         
,[IsDefault],
[IsRemovable],[MachineTypeID],[ItemType],[CreatedBy],[CreatedOn] )

    SELECT mi.Name
    ,mi.Price
    ,mi.Quantity
    ,(SELECT ID from ItemGroups WHERE NAME=mi.ItemGroup) as ID
    ,mi.SubGroup,
    CASE 
      WHEN mi.IsDefault ='Yes' THEN 1 
      WHEN mi.IsDefault ='No' THEN 0 
      WHEN mi.IsDefault IS NULL THEN 0
     END ,
     CASE 
      WHEN mi.IsRemovable ='Yes' THEN 1 
      WHEN mi.IsRemovable ='No' THEN 0 
     END ,
    (SELECT ID from MachineTypes WHERE Name=mi.MachineType),
    (SELECT ID from MachineItemTypes WHERE Name=mi.ItemType),
    mi.CreatedBy 
    ,mi.CreatedOn       
    FROM @MachineItems mi