My<cod/>.net
Как сделать вставки в таблицу?
У меня есть представление, в котором есть список заданий с данными, такими как то, кому они назначены, и этап, на котором они находятся. Мне нужно написать хранимую процедуру, которая возвращает количество заданий, которые каждый человек имеет на каждом этапе.
До сих пор у меня есть это (упрощенный):
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.
Edit: @Lee: к сожалению, InStage1 = 1 было упрощением. Это действительно больше похоже на то, где DateStarted-это не NULL, а DateFinished - это NULL.
Edit: @BCS: мне нравится идея сделать вставку всех сотрудников сначала, поэтому мне просто нужно делать обновление каждый раз. Но я изо всех сил пытаюсь получить эти утверждения UPDATE правильно.
Вы можете просто проверить наличие и использовать соответствующую команду. Я считаю, что это действительно использует курсор за кулисами, но это лучшее, что вы, вероятно, получите:
IF (EXISTS (SELECT * FROM MyTable WHERE StaffName = @StaffName))
begin
UPDATE MyTable SET ... WHERE StaffName = @StaffName
end
else
begin
INSERT MyTable ...
end
SQL2008 имеет новую функцию MERGE, которая является классной, но это не в 2005 году.
Чтобы получить реальный тип запроса "upsert", вам нужно использовать if exists... тип вещи, и это, к сожалению, означает использование курсора.
Тем не менее, вы можете выполнить два запроса, один из которых сделает ваши обновления там, где есть существующая строка, а затем вставить новую. Я думаю, что этот подход на основе набора будет предпочтительнее, если вы не имеете дело исключительно с небольшим количеством строк.
IIRC существует какой-то синтаксис "On Duplicate" (имя может быть неправильным), который позволяет вам обновлять, если строка существует (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)
Следующий запрос в таблице результатов должен снова объединить строки. Это предполагает, что 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
Мне удалось заставить его работать с вариацией ответа 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