Проверьте, существует ли запись при вставке с использованием пользовательского типа таблицы SQL Server

Я пытаюсь массово вставить записи в сервер sql. Я использую тип таблицы, определяемой пользователем, для передачи коллекции записей из моего приложения .net. Пожалуйста, взгляните на запрос на вставку ниже.

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

Что я хочу сделать, так это поставить галочку перед вставкой записей, существует ли запись с [MachineTypeID] и [Name] в таблице или нет. Если он не существует, вставьте Eles Update в запись.

Как я могу сделать это с типом таблицы, определяемым пользователем?


constraints sql-server sql-insert user-defined-types
person Sachin Trivedi    schedule 13.11.2014    source источник

Ответы (2)


arrow_upward
2
arrow_downward

  1. Вместо прямой вставки следует использовать команду MERGE. То, что вы хотите сделать, на самом деле не относится к пользовательским типам таблиц.

  2. Было бы лучше/более эффективно, если бы вы присоединились к 3 подтаблицам, а не имели подзапросы для столбцов, которые будут выполняться для каждой строки.

Пример:

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]);
person Solomon Rutzky    schedule 13.11.2014
comment
Спасибо за предложения. Я сделаю это таким образом. - person Sachin Trivedi; 13.11.2014
comment
@SachinTrivedi: Готово с обновлениями. Теперь запрос должен полностью работать. - person Solomon Rutzky; 13.11.2014
comment
этот оператор обновления дает мне ошибку. Оператор MERGE попытался выполнить ОБНОВЛЕНИЕ или УДАЛЕНИЕ одной и той же строки более одного раза. Это происходит, когда целевая строка соответствует более чем одной исходной строке. Оператор MERGE не может ОБНОВИТЬ/УДАЛИТЬ одну и ту же строку целевой таблицы несколько раз. Уточните предложение ON, чтобы целевая строка соответствовала не более чем одной исходной строке, или используйте предложение GROUP BY, чтобы сгруппировать исходные строки. - person Sachin Trivedi; 20.01.2015

arrow_upward
1
arrow_downward

Вы можете использовать Merge здесь

Использование слияния

  1. Вы можете вставить, если не существует
  2. Вы можете удалить, если уже существует
  3. Вы можете обновить, если уже существует
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
person Ganesh_Devlekar    schedule 13.11.2014
comment
Спасибо за быстрый ответ. Я новичок в использовании Merge. Один вопрос: вы объединили MachineItems.Id и @MachineItems.id. Значит ли это, что если идентификатор не совпадает, будет выполняться только вставка? - person Sachin Trivedi; 13.11.2014
comment
Столбцы @SachinTrivedi, определенные после ключевого слова ON, используемого для соответствия критериям. Теперь обновлено с помощью name, как вы сказали в вопросе. - person Ganesh_Devlekar; 13.11.2014
comment
Спасибо за разъяснение. - person Sachin Trivedi; 13.11.2014