How to make Upsert Into table?

Question

I have a view in which there is a list of tasks in it, with data such as the one to whom they are assigned, and at the stage in which they are. I need to write a stored procedure that returns the number of tasks, each of which each person has at each stage.

So far I have this (simplified):

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

The problem is that the lines are not concatenated. Therefore, if an employee has tasks in stages 1 and stage2, there are two lines in @ResultTable. I would really like to update the row if it exists for the employee, and insert a new row if it does not exist.

Does anyone know how to do this, or can suggest a different approach? I would really like to avoid using cursors to repeat in the list of users (but this is my return option).

SQL Server 2005.

: @Lee: , InStage1 = 1 . WHERE DateStarted NOT NULL DateFinished IS NULL.

: @BCS: , . UPDATE.

Answer 1

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)

Answer 2

, , , . , ?

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

Answer 3

. , , , , , :

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

SQL2008 MERGE, , 2005 .

Answer 4

"upsert", if, ... , , , .

, , , . , , , , .

Answer 5

. , 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

Answer 6

I managed to get it to work with a variation of the BCS response. This would not allow me to use a table variable, so I had to create a temporary table.

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