How to code a CTE into a MERGE Statement

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)

How to code a CTE into a MERGE Statement

Author

Topic

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2012-12-11 : 15:02:14

I have a CTE that I want to incorparate into a MERGE statement but no matter what I've tried I get Syntax errors. I was unable to find any examples with both a CTE & a MERGE. Is this even possible?

Here is what I have:


CREATE TABLE Test
(
	ID				INT				not null,
	LocID			CHAR(4)			not null, 
	TestName		VARCHAR(30)		not null,
	TestDate		SMALLDATETIME	not	null,
	Score			decimal(6,2)	not null	
)
GO

INSERT  Test 
(ID, LocID, TestName, TestDate, Score) 
VALUES
(3377, '355', 'ACT_ENG', '2012-09-27 00:00:00', 16.00),
(3377, '355', 'ACT_SCI', '2012-09-27 00:00:00', 23.00),
(1985, '355', 'ACT_MATH', '2012-07-02 00:00:00', 15.00),
(1985, '355', 'ACT_RDG', '2012-07-02 00:00:00', 17.00),
(1985, '355', 'ACT_ENG', '2012-07-02 00:00:00', 16.00),
(1985, '355', 'ACT_SCI', '2012-07-02 00:00:00', 23.00),
(7798, '355', 'ACT_MATH', '2012-08-13 00:00:00', 15.00),
(7798, '355', 'ACT_RDG', '2012-08-13 00:00:00', 17.00),
(7798, '355', 'ACT_ENG', '2012-08-13 00:00:00', 16.00),
(7798, '355', 'ACT_SCI', '2012-08-13 00:00:00', 23.00),
(5732, '344', 'ACT_MATH', '2012-05-02 00:00:00', 15.00),
(5732, '344', 'ACT_RDG', '2012-05-02 00:00:00', 17.00),
(5732, '344', 'ACT_ENG', '2012-05-02 00:00:00', 16.00),
(5732, '344', 'ACT_SCI', '2012-05-02 00:00:00', 23.00),
(2735, '344', 'ACT_MATH', '2012-08-14 00:00:00', 15.00),
(2735, '344', 'ACT_RDG', '2012-08-14 00:00:00', 16.00),
(2735, '344', 'ACT_ENG', '2012-08-14 00:00:00', 15.00),
(2735, '344', 'ACT_SCI', '2012-08-14 00:00:00', 21.00),
(2735, '344', 'ACT_MATH', '2012-08-14 00:00:00', 15.00),
(4343, '355', 'ACT_ENG', '2011-06-02 00:00:00', 16.00),
(4343, '355', 'ACT_SCI', '2011-06-02 00:00:00', 23.00),
(6831, '344', 'ACT_ENG', '2011-09-12 00:00:00', 16.00),
(6831, '344', 'ACT_SCI', '2011-09-12 00:00:00', 23.00),
(8343, '355', 'ACT_ENG', '2010-08-18 00:00:00', 16.00),
(8343, '355', 'ACT_SCI', '2010-08-18 00:00:00', 23.00),
(9831, '344', 'ACT_ENG', '2010-09-16 00:00:00', 16.00),
(9831, '344', 'ACT_SCI', '2010-09-16 00:00:00', 23.00)

GO

CREATE TABLE SchoolTerm
(
	SchoolYR		CHAR(9)			not null,
	LocID			CHAR(4)			not null, 
	Term			TINYINT			not null,
	StartDate		DATE			not	null
)
GO

INSERT  SchoolTerm 
(SchoolYR, Term, LocID, StartDate) 
VALUES
('2010-2011', 1, '355', '2010-08-17'),
('2010-2011', 2, '355', '2010-10-19'),
('2010-2011', 3, '355', '2011-01-05'),
('2010-2011', 4, '355', '2011-03-21'),
('2010-2011', 1, '344', '2010-08-17'),
('2010-2011', 2, '344', '2010-10-19'),
('2010-2011', 3, '344', '2011-01-05'),
('2010-2011', 4, '344', '2011-03-21'),

('2011-2012', 1, '355', '2011-08-16'),
('2011-2012', 2, '355', '2011-10-18'),
('2011-2012', 3, '355', '2012-01-03'),
('2011-2012', 4, '355', '2012-03-19'),
('2011-2012', 1, '344', '2011-08-16'),
('2011-2012', 2, '344', '2011-10-18'),
('2011-2012', 3, '344', '2012-01-03'),
('2011-2012', 4, '344', '2012-03-19'),

('2012-2013', 1, '355', '2012-08-14'),
('2012-2013', 2, '355', '2012-10-15'),
('2012-2013', 3, '355', '2013-01-03'),
('2012-2013', 4, '355', '2013-03-11'),
('2012-2013', 1, '344', '2012-08-14'),
('2012-2013', 2, '344', '2012-10-15'),
('2012-2013', 3, '344', '2013-01-03'),
('2012-2013', 4, '344', '2013-03-11')
GO

CREATE TABLE Scores
(
	SchoolYR		CHAR(9)			not null,
	TestName		VARCHAR(30)		not null,
	LocID			CHAR(4)			not null, 
	ID				INT				not null,
	TestDate		SMALLDATETIME	not	null,
	Score			decimal(6,2)	not null
)
GO

Here is the CTE (that VISAKH16 supplied in another thread):
this gets me the desired results.


;With CTE
AS
(
SELECT st.SchoolYR, st.LocID, st.Term,
		st.StartDate,
		COALESCE(DATEADD(dd,-1,st1.StartDate),'20200101') AS EndDate
FROM SchoolTerm st
OUTER APPLY (SELECT TOP 1 StartDate
             FROM SchoolTerm
             WHERE LocID = st.LocID
             AND StartDate > st.StartDate
             ORDER BY StartDate)st1
)

SELECT st.SchoolYr,
		t.TestName, t.LocID, t.ID, t.TestDate, t.Score
FROM  Test t
INNER JOIN CTE st
ON t.locID = st.LocID
AND t.TestDate BETWEEN st.StartDate AND st.EndDate

where
	t.TestDate >= dateadd(day,datediff(day,0,getdate())-863,0)

Now I want to put this CTE into the below MERGE:


BEGIN TRAN
  	MERGE Scores as tt
	USING
-- put the CTE here, the result of the CTE would give me i.*

ON	tt.SchoolYR = i.SchoolYR and
	tt.TestName = i.TestName and
	tt.LocID	= i.LocID and
	tt.ID		= i.ID
	WHEN MATCHED 
	THEN
		UPDATE SET
			tt.SchoolYR = i.SchoolYR,
			tt.TestName = i.TestName,
			tt.LocID	= i.LocID,
			tt.ID		= i.ID,
			tt.TestDate	= i.TestDate,
			tt.Score	= i.Score
	WHEN NOT MATCHED
	THEN
		INSERT (SchoolYR, TestName, LocID, ID, TestDate, Score)
		VALUES (i.SchoolYR, i.TestName, i.LocID, i.ID,
				i.TestDate, i.Score)
		
		OUTPUT
   $action,
   
   INSERTED.*,
   DELETED.*;
ROLLBACK TRAN
;

The result of the MERGE on the first execution should INSERT 27 rows.

Does anyone know the correct Syntax for this?

Thanks,
sqlraider

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-11 : 15:05:30

~~Put the code of the CTE where your comment is.~~ I see the issue, you want to use the statement that consumes the cte.. try making that statment a CTE also:

BEGIN TRAN

;With CTE
AS
(
SELECT st.SchoolYR, st.LocID, st.Term,
		st.StartDate,
		COALESCE(DATEADD(dd,-1,st1.StartDate),'20200101') AS EndDate
FROM SchoolTerm st
OUTER APPLY (SELECT TOP 1 StartDate
             FROM SchoolTerm
             WHERE LocID = st.LocID
             AND StartDate > st.StartDate
             ORDER BY StartDate)st1
),
CTE1 AS (

SELECT st.SchoolYr,
		t.TestName, t.LocID, t.ID, t.TestDate, t.Score
FROM  Test t
INNER JOIN CTE st
ON t.locID = st.LocID
AND t.TestDate BETWEEN st.StartDate AND st.EndDate

where
	t.TestDate >= dateadd(day,datediff(day,0,getdate())-863,0)
)

  	MERGE Scores as tt
	USING
    CTE1 AS i

ON	tt.SchoolYR = i.SchoolYR and
	tt.TestName = i.TestName and
	tt.LocID	= i.LocID and
	tt.ID		= i.ID
	WHEN MATCHED 
	THEN
		UPDATE SET
			tt.SchoolYR = i.SchoolYR,
			tt.TestName = i.TestName,
			tt.LocID	= i.LocID,
			tt.ID		= i.ID,
			tt.TestDate	= i.TestDate,
			tt.Score	= i.Score
	WHEN NOT MATCHED
	THEN
		INSERT (SchoolYR, TestName, LocID, ID, TestDate, Score)
		VALUES (i.SchoolYR, i.TestName, i.LocID, i.ID,
				i.TestDate, i.Score)
		
		OUTPUT
   $action,
   
   INSERTED.*,
   DELETED.*;
ROLLBACK TRAN
;

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2012-12-11 : 15:19:38

When I do I get Syntax errors at the underlined.

What am I doing wrong? Plus how do I get it into the alias of i?

Edited: Disregard Lamprey posted code while I was replying & the code works.

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2012-12-11 : 15:23:52

Sweet! That's exactly what I needed!

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources