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

Question

Как мне сделать 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.

3 sql sql-server tsql

Ray 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

BCS 21.08.2008 00:09:16

«на дубликате», о котором вы упоминаете, - «ВСТАВИТЬ ... НА ДУБЛИКАТ», «ОБНОВЛЕНИЕ

nickf 14.10.2008 02:16:11

AlexCuse · Answer 1

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

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

Ryan Farley · Answer 2

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

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

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

Lee · Answer 3

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

SELECT StaffName, SUM(InStage1) AS 'JobsAtStage1', SUM(InStage2) AS 'JobsAtStage2'
  FROM ViewJob
GROUP BY StaffName

Blorgbeard is out · Answer 4

Следующий запрос к вашей таблице результатов должен снова объединить строки. Это предполагает, что 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

Ray · Answer 5

Мне удалось заставить его работать с вариацией ответа 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