need help building a stored procedure that takes rows from one table into another.
- by MyHeadHurts
alright i built this stored procedure to take the columns from a stagging table and copy them into my other table, but if these four columns are duplicates it wont insert the rows, works fine.
however, what i want to do is if only the tour, taskname and deptdate are the same, then i will update the rest of the information. and if all four columns are the same dont instert.
INSERT INTO dashboardtasks1
SELECT [tour], [taskname], [deptdate], [tasktype], [desc], [duedate], [compdate], [comments], [agent], [compby], [graceperiod]
FROM staggingtasks
WHERE NOT EXISTS(SELECT *
FROM dashboardtasks1
WHERE (staggingtasks.tour=dashboardtasks1.tour and
staggingtasks.taskname=dashboardtasks1.taskname and
staggingtasks.deptdate=dashboardtasks1.deptdate and
staggingtasks.duedate=dashboardtasks1.duedate
)
)
i saw something like this
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;
but how could i do it if my stated 3 columns are the samed then update?
or is there a way to do this with an if statement and use 2 different queries, but how would my if statement work would it check if the row exists in the table i am uploading to and then run the insert statement?
or what if i did something like
IF EXISTS (SELECT * FROM dashboardtasks WHERE staggingtasks.tour=dashboardtasks.tour and
staggingtasks.taskname=dashboardtasks.taskname and
staggingtasks.deptdate=dashboardtasks.deptdate )
begin
UPDATE [dashboardtasks]
SET [tour] = staggingtasks.tour,
[taskname] = staggingtasks.taskname,
[deptdate] = staggingtasks.deptdate,
[tasktype] = staggingtasks.tasktype,
[desc] = staggingtasks.desc,
[duedate] = staggingtasks.duedate,
[compdate] = staggingtasks.compdate,
[comments] = staggingtasks.comments,
[agent] = staggingtasks.agent,
[compby] = staggingtasks.compby,
[graceperiod] = staggingtasks.graceperiod
end
else EXISTS (SELECT * FROM dashboardtasks WHERE staggingtasks.tour=dashboardtasks.tour and
staggingtasks.taskname=dashboardtasks.taskname and
staggingtasks.deptdate=dashboardtasks.deptdate and
staggingtasks.duedate=dashboardtasks.duedate )
begin
INSERT INTO dashboardtasks1
SELECT [tour], [taskname], [deptdate], [tasktype], [desc], [duedate], [compdate], [comments], [agent], [compby], [graceperiod]
FROM staggingtasks
WHERE NOT EXISTS(SELECT *
FROM dashboardtasks1
WHERE (staggingtasks.tour=dashboardtasks1.tour and
staggingtasks.taskname=dashboardtasks1.taskname and
staggingtasks.deptdate=dashboardtasks1.deptdate and
staggingtasks.duedate=dashboardtasks1.duedate
)
)
end
end
