March 4, 2014 at 5:10 am
CREATE TABLE #tblTasks
(
TaskID int,
Task varchar(20),
SortID int
)
GO
INSERT INTO #tblTasks (TaskID, Task, SortID)
SELECT 1, 'Task 1', 1 UNION ALL
SELECT 2, 'Task 2' , 2 UNION ALL
SELECT 3, 'Task 3' , 3 UNION ALL
SELECT 4, 'Task 4', 4
CREATE TABLE #tblProject_Tasks
(
TaskID int,
Task varchar(20),
SortID int
)
INSERT INTO #tblProject_Tasks(TaskID, Task, SortID)
SELECT TaskID, Task, SortID FROM #tblTasks
DROP TABLE #tblTasks
DROP TABLE #tblProject_Tasks
At this point, in terms of data, the tables are identical. And #tblProject_Tasks is 'based on' #tblTasks in the sense that the data was copied from #tblTasks.
Let's say I change the sort order of the 'base' table (#tblTasks) so that Task 3 appears before Task 2 (when sorted by SortID) - so, the data looks like:
TaskID---Task-----SortID
1______Task 1 ____1
3______Task 3_____2
2______Task 2_____3
4______Task 4_____4
How can I compare #tblTasks and #tblProject_Tasks - based on the TaskID and SortID and establish whether anything has changed?
It is possible, for example, that a task is removed from the 'base' table but not from the 'project task' table (or, as above, could be re-sorted) and I need to establish whether the #Project_Tasks table is still an exact copy of the #tblTasks or whether a change - in either table - has occurred.
It is also possible that a row is removed from the 'base' table and another row added. So both tables have the same number of rows.
I am after a query that does this.
Does #tblTasks' have any Task ID that does not appear in #tblProject_Tasks and vice versa. I guess something like this will do it:
IF EXISTS (SELECT TaskID FROM #tblTasks WHERE TaskID NOT IN (SELECT TaskID FROM #tblProject_Tasks))
OR EXISTS (SELECT TaskID FROM #tblProject_Tasks WHERE TaskID NOT IN (SELECT TaskID FROM #tblTasks))
So, scrub this one out - think I know how to do it.
March 4, 2014 at 9:52 am
You could use Full Outer Join to compare the two tables and you can use BINARY_CHECKSUM to check the difference between rows with the same id. Like so:
drop table #tblTasks;
drop table #tblProject_Tasks;
CREATE TABLE #tblTasks
(
TaskID int,
Task varchar(20),
SortID int
)
GO
INSERT INTO #tblTasks (TaskID, Task, SortID)
SELECT 1, 'Task 1', 1 UNION ALL
SELECT 2, 'Task 2' , 2 UNION ALL
SELECT 3, 'Task 3' , 3 UNION ALL
SELECT 4, 'Task 4', 4 union all
select 5, 'Task 5', 5
CREATE TABLE #tblProject_Tasks
(
TaskID int,
Task varchar(20),
SortID int
)
INSERT INTO #tblProject_Tasks(TaskID, Task, SortID)
SELECT TaskID, Task, SortID FROM #tblTasks where TaskID < 5
update #tblProject_Tasks
set SortID = case when TaskID = 2 then 3
when TaskID = 3 then 2
else SortID end
SELECT
a.TaskID
,a.Task
,a.SortID
,b.TaskID
,b.Task
,b.SortID
,case
when a.TaskID is null or b.taskID is null then 'Missing'
when BINARY_CHECKSUM(a.TaskID, a.Task, a.SortID) <> BINARY_CHECKSUM (b.TaskID, b.Task, b.SortID) then 'Different'
else 'Same'
end as RowDiff
FROM
#tblTasks a
full outer join
#tblProject_Tasks b on a.TaskID = b.TaskID
March 4, 2014 at 4:17 pm
Thank you for that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply