SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to code a CTE into a MERGE Statement		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sqlraider
Yak Posting Veteran

USA
61 Posts
Posted - 12/11/2012 :  15:02:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3859 Posts
Posted - 12/11/2012 :  15:05:30  Show Profile  Reply with Quote
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
;
Edited by - Lamprey on 12/11/2012 15:13:00
Go to Top of Page

Sqlraider
Yak Posting Veteran

USA
61 Posts
Posted - 12/11/2012 :  15:19:38  Show Profile  Reply with Quote
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.
Edited by - Sqlraider on 12/11/2012 15:29:04
Go to Top of Page

Sqlraider
Yak Posting Veteran

USA
61 Posts
Posted - 12/11/2012 :  15:23:52  Show Profile  Reply with Quote
Sweet! That's exactly what I needed!

Thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000