August 15, 2014 at 3:47 pm
I have two tables, like the ones below:
declare @tblA table ( ID int, Value int, primary key ( ID ) )
declare @tblB table ( ID int, Value int, primary key ( ID ) )
insert @tblA ( ID, Value )
values ( 1, 10 ), ( 2, 20 ), ( 3, 30 ), ( 4, null ), ( 5, 50 ), ( 6, null ), ( 8, 80 )
insert @tblB ( ID, Value )
values ( 1, 10 ), ( 2, 2 ), ( 3, 30 ), ( 4, null ), ( 5, null ), ( 6, 6 ), ( 7, 10 )
I need to identify the records in @tblA that don't exist in @tblB as "IsNew", those that exist in @tblA and @tblB, but the value of Value in @tblA is different from that in @tblB as "IsUpdate", and the records in @tblB that don't exist in @tblA is flag those as "IsDelete". I don't need to flag the identical records. I want a result set that contains the following:
ID Value IsNew IsUpdate IsDelete
-- ----- ------ -------- -----------
2 20 0 1 0
5 50 0 1 0
6 NULL 0 1 0
7 NULL 0 0 1
8 80 1 0 0
I managed to get this done in one step, but I'm far from convinced this will perform for large data sets. I would appreciate different suggestions. Here's how I solved:
;WITH A_UNION_B AS
(
SELECT ID FROM @tblA
UNION
SELECT ID FROM @tblB
),A_EXCEPT_B AS
(
SELECT ID, Value FROM @tblA
EXCEPT
SELECT ID, Value FROM @tblB
), B_EXCEPT_A AS
(
SELECT ID, Value FROM @tblB
EXCEPT
SELECT ID, Value FROM @tblA
)
SELECTID = AUB.ID,
ValueA = AEB.Value,
IsNew = CASE WHEN BEA.ID IS NULL THEN 1 ELSE 0 END,
IsUpdate = CASE WHEN AEB.ID IS NOT NULL AND BEA.ID IS NOT NULL THEN 1 ELSE 0 END,
IsDelete = CASE WHEN AEB.ID IS NULL THEN 1 ELSE 0 END
FROMA_UNION_B AUB LEFT JOIN
A_EXCEPT_B AEB ON
AEB.ID = AUB.ID LEFT JOIN
B_EXCEPT_A BEA ON
BEA.ID = AUB.ID
WHEREAEB.ID IS NOT NULL OR BEA.ID IS NOT NULL
August 15, 2014 at 5:33 pm
Here's some SQL2K5-friendly DDL, in case anyone else is stuck in the stone ages like me:
declare @tblA table ( ID int, Value int, primary key ( ID ) )
declare @tblB table ( ID int, Value int, primary key ( ID ) )
insert @tblA ( ID, Value )
SELECT 1, 10 UNION ALL
SELECT 2, 20 UNION ALL
SELECT 3, 30 UNION ALL
SELECT 4, null UNION ALL
SELECT 5, 50 UNION ALL
SELECT 6, null UNION ALL
SELECT 8, 80
insert @tblB ( ID, Value )
SELECT 1, 10 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 3, 30 UNION ALL
SELECT 4, null UNION ALL
SELECT 5, null UNION ALL
SELECT 6, 6 UNION ALL
SELECT 7, 10
;
And here's what I came up with just messing around. Probably way worse than yours, and I'm sure someone else will come along and confirm that mine is poop, but it was fun to think about it. WITH
CTE1 AS
(
SELECT
B_ID = ISNULL([@tblB].ID, 0),
B_Value = ISNULL([@tblB].Value, 0),
A_ID = ISNULL([@tblA].ID, 0),
A_Value = ISNULL([@tblA].Value , 0)
FROM @tblA
FULL OUTER JOIN @tblB ON
[@tblA].ID = [@tblB].ID
),
CTE2 AS
(
SELECT
ID = CASE WHEN A_ID = 0 THEN B_ID ELSE A_ID END,
Value = NULLIF(A_Value, 0),
IsNew = CASE WHEN B_ID - A_ID < 0 THEN 1 ELSE 0 END,
IsUpdate = CASE WHEN B_ID = A_ID AND B_Value <> A_Value THEN 1 ELSE 0 END,
IsDelete = CASE WHEN B_ID - A_ID > 0 THEN 1 ELSE 0 END
FROM CTE1
)
SELECT * FROM CTE2
WHERE IsNew + IsUpdate + IsDelete = 1
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply