How do I do an Upsert Into Table?

Question

I have a view that has a list of jobs in it, with data like who they're assigned to and the stage they are in. I need to write a stored procedure that returns how many jobs 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 with that is that the rows don't combine. So if a staff member has jobs in stage1 and stage2 there's two rows in @ResultTable. What I would really like to do is to update the row if one exists for the staff member and insert a new row if one doesn't exist.

Does anyone know how to do this, or can suggest a different approach? I would really like to avoid using cursors to iterate on the list of users (but that's my fall back option).

I'm using SQL Server 2005.

Edit: @Lee: Unfortunately the InStage1 = 1 was a simplification. It's really more like WHERE DateStarted IS NOT NULL and DateFinished IS NULL.

Edit: @BCS: I like the idea of doing an insert of all the staff first so I just have to do an update every time. But I'm struggling to get those UPDATE statements correct.

Answer 1

To get a real "upsert" type of query you need to use an if exists... type of thing, and this unfortunately means using a cursor.

However, you could run two queries, one to do your updates where there is an existing row, then afterwards insert the new one. I'd think this set-based approach would be preferable unless you're dealing exclusively with small numbers of rows.

Answer 2

You could just check for existence and use the appropriate command. I believe this really does use a cursor behind the scenes, but it's the best you'll likely get:

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

SQL2008 has a new MERGE capability which is cool, but it's not in 2005.

Answer 3

Actually, I think you're making it much harder than it is. Won't this code work for what you're trying to do?

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

Answer 4

The following query on your result table should combine the rows again. This is assuming that InStage1 and InStage2 are never both '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 5

IIRC there is some sort of "On Duplicate" (name might be wrong) syntax that lets you update if a row exists (MySQL)

Alternately some form of:

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 6

I managed to get it working with a variation of BCS's answer. It wouldn't let me use a table variable though, so I had to make a temp 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