Как мне сделать Upsert Into Table?

У меня есть представление, в котором есть список заданий, с такими данными, как то, кому они назначены, и этап, на котором они находятся. Мне нужно написать хранимую процедуру, которая возвращает количество заданий, которые есть у каждого человека на каждом этапе.

Пока у меня есть это (упрощенно):

DECLARE @ResultTable table 
(
  StaffName nvarchar(100),
  Stage1Count int,
  Stage2Count int
)

INSERT INTO @ResultTable (StaffName, Stage1Count)
  SELECT StaffName, COUNT(*) FROM ViewJob
  WHERE InStage1 = 1
  GROUP BY StaffName

INSERT INTO @ResultTable (StaffName, Stage2Count)
  SELECT StaffName, COUNT(*) FROM ViewJob
  WHERE InStage2 = 1
  GROUP BY StaffName

Проблема в том, что строки не объединяются. Поэтому, если у сотрудника есть рабочие места в stage1 и stage2, в @ResultTable есть две строки. Что я действительно хотел бы сделать, это обновить строку, если она существует для сотрудника, и вставить новую строку, если она не существует.

Кто-нибудь знает, как это сделать, или может предложить другой подход? Я действительно хотел бы избежать использования курсоров для итерации по списку пользователей (но это мой запасной вариант).

Я использую SQL Server 2005.

Редактировать: @Lee: К сожалению, InStage1 = 1 было упрощением. Это действительно больше похоже на то, ГДЕ DateStarted НЕ НУЛЬ, а DateFinished НУЛЕ.

Редактировать: @BCS: Мне нравится идея сделать вставку всех сотрудников в первую очередь, поэтому мне просто нужно каждый раз обновлять. Но я изо всех сил пытаюсь получить правильные утверждения UPDATE.

20.08.2008 23:46:13
6 ОТВЕТОВ
РЕШЕНИЕ

Во IIRC есть некоторый синтаксис «На дубликате» (имя может быть неправильным), который позволяет обновлять, если существует строка (MySQL)

Альтернативно какая-то форма:

INSERT INTO @ResultTable (StaffName, Stage1Count, Stage2Count)
  SELECT StaffName,0,0 FROM ViewJob
  GROUP BY StaffName

UPDATE @ResultTable Stage1Count= (
  SELECT COUNT(*) AS count FROM ViewJob
  WHERE InStage1 = 1
  @ResultTable.StaffName = StaffName)

UPDATE @ResultTable Stage2Count= (
  SELECT COUNT(*) AS count FROM ViewJob
  WHERE InStage2 = 1
  @ResultTable.StaffName = StaffName)
1
21.08.2008 00:09:16
«на дубликате», о котором вы упоминаете, - «ВСТАВИТЬ ... НА ДУБЛИКАТ», «ОБНОВЛЕНИЕ
nickf 14.10.2008 02:16:11

Чтобы получить реальный тип запроса "upsert", вам нужно использовать тип if if ... и это, к сожалению, означает использование курсора.

Однако вы можете выполнить два запроса: один для обновления, где есть существующая строка, а затем вставить новый. Я думаю, что этот подход на основе множеств предпочтительнее, если вы не имеете дело только с небольшим количеством строк.

1
20.08.2008 23:55:04

Вы можете просто проверить наличие и использовать соответствующую команду. Я считаю, что это действительно использует курсор за кулисами, но это лучшее, что вы, вероятно, получите:

IF (EXISTS (SELECT * FROM MyTable WHERE StaffName = @StaffName))
begin
    UPDATE MyTable SET ... WHERE StaffName = @StaffName
end
else
begin
    INSERT MyTable ...
end 

В SQL2008 появилась новая возможность MERGE, которая хороша, но не в 2005 году.

2
20.08.2008 23:57:20

На самом деле, я думаю, ты делаешь это намного сложнее, чем есть. Не сработает ли этот код для того, что вы пытаетесь сделать?

SELECT StaffName, SUM(InStage1) AS 'JobsAtStage1', SUM(InStage2) AS 'JobsAtStage2'
  FROM ViewJob
GROUP BY StaffName
3
20.08.2008 23:58:32

Следующий запрос к вашей таблице результатов должен снова объединить строки. Это предполагает, что InStage1 и InStage2 никогда не равны '1'.

select distinct(rt1.StaffName), rt2.Stage1Count, rt3.Stage2Count
from @ResultTable rt1
left join @ResultTable rt2 on rt1.StaffName=rt2.StaffName and rt2.Stage1Count is not null
left join @ResultTable rt3 on rt1.StaffName=rt2.StaffName and rt3.Stage2Count is not null
0
21.08.2008 00:03:12

Мне удалось заставить его работать с вариацией ответа BCS. Это не позволило бы мне использовать переменную таблицы, поэтому мне пришлось создать временную таблицу.

CREATE TABLE #ResultTable
(
  StaffName nvarchar(100),
  Stage1Count int,
  Stage2Count int
)

INSERT INTO #ResultTable (StaffName)
  SELECT StaffName FROM ViewJob
  GROUP BY StaffName

UPDATE #ResultTable SET 
  Stage1Count= (
    SELECT COUNT(*) FROM ViewJob V
    WHERE InStage1 = 1 AND 
        V.StaffName = @ResultTable.StaffName COLLATE Latin1_General_CI_AS
    GROUP BY V.StaffName),
  Stage2Count= (
    SELECT COUNT(*) FROM ViewJob V
    WHERE InStage2 = 1 AND 
        V.StaffName = @ResultTable.StaffName COLLATE Latin1_General_CI_AS
    GROUP BY V.StaffName)

SELECT StaffName, Stage1Count, Stage2Count FROM #ResultTable

DROP TABLE #ResultTable
0
9.04.2017 14:20:12