Stored procedure, dealing with insert and update, is this impossible?

I have two tables with the exact same columns.

Both have primary identity keys called id that auto increment.

My program adds data to a stagging table and then filters what gets added to the task table through this procedure.

Mmy problem is I have this update statement followed by an insert, for some reason my update statement does not work, when it is in the procedure by itself.

My insert statement works when it is by itself in the procedure but not when underneath this update statement.

I want my update statement to

Update my duedate in my dashboardtasks table, if these 3 fields match in the row tour deptdate taskname

If the row being added does not match in these 3 fields than insert the row as a new row using my insert statement underneath.

 update dashboardtasks set
         deptdate = s.deptdate,
         tour = s.tour,
          tasktype = s.tasktype,
          [desc] = s.[desc],
          duedate = s.duedate,
          compdate = s.compdate,
          comments = s.comments,
          agent = s.agent,
          compby = s.compby,
          graceperiod    = s.graceperiod
        from staggingtasks as s
        where
          s.tour=dashboardtasks.tour and
          s.taskname=dashboardtasks.taskname and 
          s.deptdate=dashboardtasks.deptdate 


        insert into dashboardtasks (tour, taskname, deptdate, tasktype, [desc], duedate, compdate, comments, agent, compby, graceperiod)
        select tour, taskname, deptdate, tasktype, [desc], duedate, comp开发者_运维问答date, comments, agent, compby, graceperiod
        from staggingtasks as s
        where not exists (select *
                          from dashboardtasks as d
                          where s.tour=d.tour and
                                s.taskname=d.taskname and 
                                s.deptdate=d.deptdate and
                                s.duedate=d.duedate
                         )


id int Unchecked
tour varchar(50) Checked
taskname varchar(50) Checked
deptdate varchar(50) Checked
tasktype varchar(50) Checked
[desc] varchar(MAX) Checked
duedate varchar(50) Checked
compdate varchar(50) Checked
comments varchar(MAX) Checked
agent varchar(50) Checked
compby varchar(50) Checked
graceperiod varchar(50) Checked

these are my fields, but compby, comments, compdate, and desc are null

---

Here is a run through with your queries.

Set up test data

create table dashboardtasks1(id int identity, tour int, taskname nvarchar(50), deptdate datetime, tasktype nvarchar(50), [desc] nvarchar(50), duedate datetime, compdate datetime, comments nvarchar(50), agent nvarchar(50), compby int, graceperiod int)
create table staggingtasks(id int, tour int, taskname nvarchar(50), deptdate datetime, tasktype nvarchar(50), [desc] nvarchar(50), duedate datetime, compdate datetime, comments nvarchar(50), agent nvarchar(50), compby int, graceperiod int)

insert into staggingtasks(id, tour, taskname, deptdate, tasktype, [desc], duedate, compdate, comments, agent, compby, graceperiod)
    values (62, 3647, 'Request Space', '2011-03-30', 'Land', NULL, '2010-01-06', NULL, NULL, 'PEGGYH', NULL, NULL)
insert into staggingtasks(id, tour, taskname, deptdate, tasktype, [desc], duedate, compdate, comments, agent, compby, graceperiod)
    values (81, 505, 'Rel. Space', '2012-02-22', 'Land', NULL, '2011-12-24', NULL, NULL, 'IMANA', NULL, NULL)
insert into staggingtasks(id, tour, taskname, deptdate, tasktype, [desc], duedate, compdate, comments, agent, compby, graceperiod)
    values (82, 505, 'Ticket', '2012-02-22', 'Air', NULL, '2012-01-08', NULL, NULL, 'SYLVIAT', NULL, NULL)
insert into staggingtasks(id, tour, taskname, deptdate, tasktype, [desc], duedate, compdate, comments, agent, compby, graceperiod)
    values (83, 505, 'Names to Airlines', '2012-02-22', 'Air', NULL, '2012-01-08', NULL, NULL, 'SYLVIAT', NULL, NULL)
insert into staggingtasks(id, tour, taskname, deptdate, tasktype, [desc], duedate, compdate, comments, agent, compby, graceperiod)
    values (90, 505, 'Names to Airlines', '2012-02-22', 'Air', NULL, '2012-01-01', NULL, NULL, 'SYLVIAT', NULL, NULL)
insert into staggingtasks(id, tour, taskname, deptdate, tasktype, [desc], duedate, compdate, comments, agent, compby, graceperiod)
    values (92, 505, 'Names to Airlines', '2012-02-22', 'Air', NULL, '2012-01-01', NULL, NULL, 'SYLVIAT', NULL, NULL)

insert into dashboardtasks1(tour, taskname, deptdate, tasktype, [desc], duedate, compdate, comments, agent, compby, graceperiod)
    values (3647, 'Request Space', '2011-03-30', 'Land', NULL, '2010-11-06', NULL, NULL, 'PEGGYH', NULL, NULL)
insert into dashboardtasks1(tour, taskname, deptdate, tasktype, [desc], duedate, compdate, comments, agent, compby, graceperiod)
    values (505, 'Rel. Space', '2012-02-22', 'Land', NULL, '2011-11-24', NULL, NULL, 'IMANA', NULL, NULL)
insert into dashboardtasks1(tour, taskname, deptdate, tasktype, [desc], duedate, compdate, comments, agent, compby, graceperiod)
    values (505, 'Ticket', '2012-02-22', 'Air', NULL, '2012-11-08', NULL, NULL, 'SYLVIAT', NULL, NULL)
insert into dashboardtasks1(tour, taskname, deptdate, tasktype, [desc], duedate, compdate, comments, agent, compby, graceperiod)
    values (505, 'Names to Airlines', '2012-02-22', 'Air', NULL, '2012-11-08', NULL, NULL, 'SYLVIAT', NULL, NULL)

Run the update statement

update dashboardtasks1 set
  tasktype = s.tasktype,
  [desc] = s.[desc],
  duedate = s.duedate,
  compdate = s.compdate,
  comments = s.comments,
  agent = s.agent,
  compby = s.compby,
  graceperiod    = s.graceperiod
from staggingtasks as s
where
  s.tour=dashboardtasks1.tour and
  s.taskname=dashboardtasks1.taskname and 
  s.deptdate=dashboardtasks1.deptdate

Four rows affected. A select from dashboardtasks1 gives you this result

id  tour    taskname    deptdate    tasktype    desc    duedate compdate    comments    agent   compby  graceperiod
1   3647    Request Space   2011-03-30 00:00:00.000 Land    NULL    2010-01-06 00:00:00.000 NULL    NULL    PEGGYH  NULL    NULL
2   505 Rel. Space  2012-02-22 00:00:00.000 Land    NULL    2011-12-24 00:00:00.000 NULL    NULL    IMANA   NULL    NULL
3   505 Ticket  2012-02-22 00:00:00.000 Air NULL    2012-01-08 00:00:00.000 NULL    NULL    SYLVIAT NULL    NULL
4   505 Names to Airlines   2012-02-22 00:00:00.000 Air NULL    2012-01-08 00:00:00.000 NULL    NULL    SYLVIAT NULL    NULL

Run the insert statement

insert into dashboardtasks1 (tour, taskname, deptdate, tasktype, [desc], duedate, compdate, comments, agent, compby, graceperiod)
select tour, taskname, deptdate, tasktype, [desc], duedate, compdate, comments, agent, compby, graceperiod
from staggingtasks as s
where not exists (select *
                  from dashboardtasks1 as d
                  where s.tour=d.tour and
                        s.taskname=d.taskname and 
                        s.deptdate=d.deptdate and
                        s.duedate=d.duedate
                 )

Two rows are affected. A query against dashboardtasks1 gives you this result.

id  tour    taskname    deptdate    tasktype    desc    duedate compdate    comments    agent   compby  graceperiod
1   3647    Request Space   2011-03-30 00:00:00.000 Land    NULL    2010-01-06 00:00:00.000 NULL    NULL    PEGGYH  NULL    NULL
2   505 Rel. Space  2012-02-22 00:00:00.000 Land    NULL    2011-12-24 00:00:00.000 NULL    NULL    IMANA   NULL    NULL
3   505 Ticket  2012-02-22 00:00:00.000 Air NULL    2012-01-08 00:00:00.000 NULL    NULL    SYLVIAT NULL    NULL
4   505 Names to Airlines   2012-02-22 00:00:00.000 Air NULL    2012-01-08 00:00:00.000 NULL    NULL    SYLVIAT NULL    NULL
5   505 Names to Airlines   2012-02-22 00:00:00.000 Air NULL    2012-01-01 00:00:00.000 NULL    NULL    SYLVIAT NULL    NULL
6   505 Names to Airlines   2012-02-22 00:00:00.000 Air NULL    2012-01-01 00:00:00.000 NULL    NULL    SYLVIAT NULL    NULL

Is this the expected behavior/result?

---

Just throwing it out there, but have you tried replacing the where clause with an inner join? Something like this:

update dashboardtasks set
     deptdate = s.deptdate,
     tour = s.tour,
      tasktype = s.tasktype,
      [desc] = s.[desc],
      duedate = s.duedate,
      compdate = s.compdate,
      comments = s.comments,
      agent = s.agent,
      compby = s.compby,
      graceperiod    = s.graceperiod
    from staggingtasks as s
    inner join dashboardtasks on
      s.tour=dashboardtasks.tour and
      s.taskname=dashboardtasks.taskname and 
      s.deptdate=dashboardtasks.deptdate 

---

Have you tried putting in a SELECT statement between the UPDATE and INSERT statements? It might give you some clues as to why the procedure isn't working as you expect.

Also the s.duedate=d.duedate part of your where clause is probably redundant.