I'm trying to run the following the following MERGE statement through a DM procedure and it errors indicating that MERGE statements require a semi-colon (this syntax is required by SLQ Server). However, DM does not accept semi-colon.

DOes anyone know of any workarounds for running SQL MERGE statements


MERGE INTO d_calendar t
USING
(
SELECT c.definition_id,
CONVERT(CHAR(4),DATEPART(yyyy,current_timestamp))+RIGHT(REPLICATE('0',2)+ CAST(DATEPART(mm,current_timestamp) AS VARCHAR(2)),2)+RIGHT(REPLICATE('0',2)+ CAST(DATEPART(d,current_timestamp) AS VARCHAR(2)),2) curr_datestring,
RIGHT(REPLICATE('0',2)+ CAST(DATEPART(mm,current_timestamp) AS VARCHAR(2)),2) curr_month,
CAST(DATEPART(q,current_timestamp) AS CHAR(1)) curr_quarter,
CONVERT(CHAR(4),DATEPART(yyyy,current_timestamp)) curr_year,
DATENAME(dy,current_timestamp) curr_running_day_cnt,
DATEDIFF(m,sdate, current_timestamp) months_ago,
DATEDIFF(d,sdate, current_timestamp) days_ago,
CASE WHEN
(CONVERT(CHAR(4),DATEPART(yyyy,current_timestamp))+RIGHT(REPLICATE('0',2)+ CAST(DATEPART(mm,current_timestamp) AS VARCHAR(2)),2)) = SUBSTRING(CONVERT(CHAR(8),datestring),1,6)
THEN
'Current Month'
WHEN
CONVERT(CHAR(4),DATEPART(yyyy,DATEADD(mm,-1,current_timestamp))) +RIGHT(REPLICATE('0',2)+ CAST(DATEPART(mm,DATEADD(mm,-1,current_timestamp)) AS VARCHAR(2)),2) = SUBSTRING(CONVERT(CHAR(8),datestring),1,6)
THEN
'Last Month'
WHEN datestring < CONVERT[CHAR[4),DATEPART[yyyy,current_timestamp))+RIGHT[REPLICATE['0',2)+ CAST[DATEPART[mm,current_timestamp) AS VARCHAR[2)),2)+RIGHT[REPLICATE['0',2)+ CAST[DATEPART[d,current_timestamp) AS VARCHAR[2)),2)
THEN
CONVERT(VARCHAR(4),DATEDIFF(m,sdate, current_timestamp)) + ' Months Ago'
ELSE
null
END relative_month_desc
FROM d_calendar c
) s
ON (s.definition_id = t.definition_id)
WHEN MATCHED THEN
UPDATE SET
t.curr_datestring = s.curr_datestring,
t.curr_month = s.curr_month,
t.curr_quarter = s.curr_quarter,
t.curr_year = s.curr_year,
t.curr_running_day_cnt = s.curr_running_day_cnt,
t.relative_month_desc = s.relative_month_desc,
t.months_ago = s.months_ago,
t.days_ago = s.days_ago


END


-EXIT

This message has been edited. Last edited by: <Kathryn Henning>,

My best advise is to wrap this Merge statment into a sql procedure and execute the procedure

same time sql procedure is is pre compiled and cached will also give u better performance on sql server side

you can also use output caluse to get modified data and report in the procedure

ENGINE SQL DEFAULT_CONNECTION NAME
SQL SQLMSS EX procname

END

Thanks. This works fine.