Here is the situation that I'm facing:
I have two tables A and B. If records are in table A and not in table B they need to be added to table B. If records are in table B and not in table A, then they need to be removed out of table B. The trick here is that it is the mixture of two keys that makes the unique combination
Table A
Operation_Key Part_Key
1 1
1 2
2 1
2 3
Table B
Operation_Key Part_Key Record_Key
1 1 1
2 1 2
2 3 3
2 4 4
I am trying to get the right type of query so that the results returned look like
Results
Operation_Key Part_Key Record_Key Action
1 2 NULL Add
2 4 4 Delete
The query I have so far looks like similar to this:
CREATE TABLE #Action_Table
(
Action VARCHAR(6),
Action_Bit INT,
Operation_Key INT,
Record_Key INT,
Part_Key INT
)
INSERT INTO #Action_Table
SELECT
CASE
WHEN WS.Operation_Key IS NULL THEN 'Delete'
WHEN WS.Operation_Key IS NOT NULL THEN 'Add'
END Action,
CASE
WHEN WS.Operation_Key IS NULL THEN '0'
WHEN WS.Operation_Key IS NOT NULL THEN '1'
END Action_Bit,
CASE
WHEN WS.Operation_Key IS NULL THEN WC.Operation_Key
WHEN WS.Operation_Key IS NOT NULL THEN WS.Operation_Key
END Operation_Key,
CASE
WHEN WS.Operation_Key IS NULL THEN WC.Record_Key
WHEN WS.Operation_Key IS NOT NULL THEN NULL
END Workcenter_Component_Key,
CASE
WHEN WS.Operation_Key IS NULL THEN WC.Part_Key
WHEN WS.Operation_Key IS NOT NULL THEN WS.Part_Key
END Part_Key
FROM #WS_Part_Table WS
FULL OUTER JOIN #WC_Part_Table WC
ON WC.Part_Key = WS.Part_Key
AND WC.Operation_Key = WS.Operation_Key
WHERE (WS.Part_Key IS NULL or WC.Part_Key IS NULL) AND (WS.Operation_Key IS NULL or WC.Operation_Key IS NULL)
Both the #WS_Part_Table and the #WC_Part_Table are temp tables that I'm constructing using queries, but my dilemma is that I have to PRE-QUERY the #WC_Part_Table query on the operation key that I'm interested in, otherwise I get way too many results.
This is the query that I'm using to create the #WC_Part_Table
CREATE TABLE #WC_Part_Table
(
Operation_Key INT,
Record_Key INT,
Part_Key INT
)
-- Workcenter Component Table
INSERT INTO #WC_Part_Table
SELECT
O.Operation_Key,
WC.Record_Key,
WC.Part_Key
FROM Workcenter_Component WC
JOIN Operation O
ON O.Default_Workcenter_Key = WC.Workcenter_Key
/* There is some reason why this next line is needed */
WHERE O.Operation_Key = 23149
Tyr this to get the results that you have posted:
SELECT COALESCE(a.Operation_Key, b.Operation_Key) Operation_Key,
COALESCE(a.Part_Key, b.Part_Key) Part_Key,
Record_Key,
CASE
WHEN Record_Key IS NULL THEN 'Add'
ELSE 'Delete'
END Action
FROM TableA a FULL OUTER JOIN TableB b
ON a.Operation_Key = b.Operation_Key
AND a.Part_Key = b.Part_Key
WHERE (a.Operation_Key IS NULL) OR (b.Operation_Key IS NULL)
Test Script:
CREATE TABLE #TableA
(
Operation_Key INT,
Part_Key INT
)
INSERT INTO #TableA
SELECT 1,1
UNION
SELECT 1,2
UNION
SELECT 2,1
UNION
SELECT 2,3
CREATE TABLE #TableB
(
Operation_Key INT,
Part_Key INT,
Record_Key INT
)
INSERT INTO #TableB
SELECT 1,1,1
UNION
SELECT 2,1,2
UNION
SELECT 2,3,3
UNION
SELECT 2,4,4
SELECT COALESCE(a.Operation_Key, b.Operation_Key) Operation_Key,
COALESCE(a.Part_Key, b.Part_Key) Part_Key,
Record_Key,
CASE
WHEN Record_Key IS NULL THEN 'Add'
ELSE 'Delete'
END Action
FROM #TableA a FULL OUTER JOIN #TableB b
ON a.Operation_Key = b.Operation_Key
AND a.Part_Key = b.Part_Key
WHERE (a.Operation_Key IS NULL) OR (b.Operation_Key IS NULL)
Output:
Operation_Key Part_Key Record_Key Action
1 2 NULL Add
2 4 4 Delete