MERGE + CTE = Filtered Target (FTW)!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

MERGE + CTE = Filtered Target (FTW)!

Author

Topic

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-02-02 : 18:55:43

Not really a question, maybe more of a post to see if anyone has comments on this technique.

First, a little background. I'm working on a DSS that needs to get data from multiple sources and the project is running on SQL 2008. I wanted to use the new MERGE command, but kept hitting a road block because I need to deactivate entries that don't appear in the data source (NOT MATCHED BY SOURCE), but I can't affect rows from any other source. Maybe it's obvious to some, but aggressive research didn't unearth anything that addressed this scenario. Unsatisfied by the alternatives, I finally found something that works through sure determination, so I wanted to share it as it's a pattern I'm sure others will come across.

Here's an example of my problem (consider column [b] to be the Source System Identifier):


-- Sample Data Source (1 of 2)
declare @t1 table (a int, b int, d int);
-- Target
declare @t2 table (a int, b int, d int);

insert @t1 values (0,1,1),(2,1,1),(5,1,1);
insert @t2 values (1,1,0),(2,1,0),(3,1,0),(4,1,0),(1,2,0),(2,2,0);

/*
a           b           d
----------- ----------- -----------
0           1           1
2           1           1
5           1           1

(3 row(s) affected)

a           b           d
----------- ----------- -----------
1           1           0
2           1           0
3           1           0
4           1           0
1           2           0
2           2           0

(6 row(s) affected)
--*/


MERGE	@t2
AS		TGT
USING	@t1
AS		SRC
ON		TGT.a
=		SRC.a
AND		TGT.b
=		SRC.b

WHEN	MATCHED
THEN	UPDATE
SET		TGT.d
=		SRC.d
WHEN	NOT MATCHED BY TARGET
THEN	INSERT
		(a,b,d)
VALUES	(SRC.a,SRC.b,SRC.D)
WHEN	NOT MATCHED BY SOURCE
THEN	DELETE;

select * from @t1;
select * from @t2;

/*
a           b           d
----------- ----------- -----------
0           1           1
2           1           1
5           1           1

(3 row(s) affected)

a           b           d
----------- ----------- -----------
2           1           1
5           1           1
0           1           1

(3 row(s) affected)
--*/

As expected, we lost data where b = 2 because it didn't match our source.

To solve for that, just add a CTE for the target with a WHERE clause restricting the resultset:



WITH	t2
AS
(
SELECT	*
FROM	@t2 t
WHERE	EXISTS
		(
		SELECT * FROM @t1 r WHERE r.b = t.b
		)
)
MERGE	t2
AS		TGT
USING	@t1
AS		SRC
ON		TGT.a
=		SRC.a
AND		TGT.b
=		SRC.b

WHEN	MATCHED
THEN	UPDATE
SET		TGT.d
=		SRC.d
WHEN	NOT MATCHED BY TARGET
THEN	INSERT
		(a,b,d)
VALUES	(SRC.a,SRC.b,SRC.D)
WHEN	NOT MATCHED BY SOURCE
THEN	DELETE;

select * from @t1;
select * from @t2;

/*
a           b           d
----------- ----------- -----------
0           1           1
2           1           1
5           1           1

(3 row(s) affected)

a           b           d
----------- ----------- -----------
2           1           1
1           2           0
2           2           0
5           1           1
0           1           1

(5 row(s) affected)
--*/

Now, we can MERGE multiple data sources into a consolidated target effectively. WOOT!

SQL_Cujo
Starting Member

1 Post

Posted - 2010-04-22 : 18:59:50

This is pretty much the only approach I've figured out to restrict the number of target table records in a MERGE statement. Did this approach end up working for you?

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-07 : 11:38:48

Yes, this worked perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources