I am trying to create a DTS package.
I have two tables tbl_A and tbl_B with similar data/rows but no
tbl_A is master.
I would like this package to query tbl_A and tbl_B and find
1)all rows in tbl_A that are different in tbl_B, 2)all rows in tbl_A
that are not present in tbl_B and
3)all rows in tbl_B that are not present in tbl_A, and then just show
Can this be done with a simple UNION?
Perhaps this could produce a temp Table that can be dropped once the
DTS package exists successfully.
The 2nd part after all the above rows are retrieved is that I would
like to add an addional Column to the retrieved data called STATUS
which has 3 possible values(letters) at the end of each row...
M (modified) means that row exists in tbl_B but has 1 or more
A (add) means this row exists in tbl_A but not in tbl_B
D (delete) means this row exists in tbl_B but not in tbl_A
I'm hopping this DTS package would output a nice comma seperated TXT
file with only...
1) rows from tbl_A that are different in tbl_B (STATUS M)
2) rows from tbl_A that are not present in tbl_B (STATUS A)
3) rows from tbl_B that are not present in tbl_A (STATUS D)
Can a DTS package in MS SQL be used to perfom all of the above tasks?
I would very much appreciate any help or any advise.
Thanks in advance :-)