Formatting MERGE

I've create a snippet for MERGE, based on judahr's one here (by the way by, thanks for it):

	Code:
	MERGE INTO $OBJECT(ins_qualname, table, view)$ AS t USING ( SELECT FROM ) AS s ON "AND s."$COLUMNS(vertical, keys)$" = t."$COLUMNS(vertical, keys)$ WHEN MATCHED THEN UPDATE SET "t."$COLUMNS(vertical,updatable)$" = s."$COLUMNS(vertical,updatable)$ WHEN NOT MATCHED BY TARGET THEN INSERT ( $COLUMNS(vertical)$ ) VALUES ( s.$COLUMNS(vertical)$ ) WHEN NOT MATCHED BY SOURCE THEN DELETE ;

I've also created a formatting rule for MERGE:

	Code:
	MERGE INTO ... AS t USING ( ... ) AS s ON ... = ... AND ... OR ... WHEN MATCHED THEN UPDATE ... SET ... = ... WHEN NOT MATCHED BY TARGET THEN INSERT ... ( ... ,... ) VALUES ( ... ,... ) WHEN NOT MATCHED BY SOURCE THEN DELETE ;

Triggering a snippet for table1 (and adding table2 with relevant parts) results in:

Code:

MERGE INTO dbo.table1 AS t
USING (
   SELECT
      t2.id1_table2
      ,t2.id2_table2
      ,t2.t2column1
      ,t2.t2column2
      ,t2.t2column3
   FROM
      dbo.table2 AS t2
   ) AS s
ON s.id1_table2 = t.id1_table1
AND s.id2_table2 = t.id2_table1
WHEN MATCHED THEN
UPDATE
SET t.id1_table1 = s.id1_table1,
t.id2_table1 = s.id2_table1,
t.t2column1 = s.t2column1,
t.t2column2 = s.t2column2,
t.t2column3 = s.t2column3
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
id1_table1,
id2_table1,
t2column1,
t2column2,
t2column3
)
VALUES
(
s.id1_table1,
s.id2_table1,
s.t2column1,
s.t2column2,
s.t2column3
)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
;

Now there must be some errors in my formatting rule, because after applying the rule to the code, the merge search condition is a bit weird:

Code:

MERGE INTO dbo.table1 AS t
USING (
SELECT
   t2.id1_table2
   ,t2.id2_table2
   ,t2.t2column1
   ,t2.t2column2
   ,t2.t2column3
FROM
   dbo.table2 AS t2
) AS s
ON s
.id1_table2 = t.id1_table1
AND s
.id2_table2 = t.id2_table1
WHEN MATCHED THEN
UPDATE
SET t.id1_table1 = s.id1_table1
,t.id2_table1 = s.id2_table1
,t.t2column1 = s.t2column1
,t.t2column2 = s.t2column2
,t.t2column3 = s.t2column3
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
id1_table1
,id2_table1
,t2column1
,t2column2
,t2column3
)
VALUES
(
s.id1_table1
,s.id2_table1
,s.t2column1
,s.t2column2
,s.t2column3
)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
;

Any hints on how to correct it?

None? :(

Thank you. I'm not sure why you're getting such result, it seems like other formatting rules get in the way

I will need to check with our development team on what can be done to get the required result.

Provided that this is going to be solved sometime, it is still an issue in 6.4.199 preview.

Hi,

Any progress on this one?

The issue is in the formatting rule that contains "AS s" which is non-keyword and syntax-only element. Keywords and syntax elements are used as anchors. Anything extra could throw off the formatting.

Please change formatting rule to the following

	Code:
	MERGE INTO ... AS t USING ( ... ) AS ... ON ... = ... AND ... OR ... WHEN MATCHED THEN UPDATE ... SET ... = ... WHEN NOT MATCHED BY TARGET THEN INSERT ... ( ... ,... ) VALUES ( ... ,... ) WHEN NOT MATCHED BY SOURCE THEN DELETE ;

Interesting. I'll give it a try. But won't "AS t" get in the way as well?

You are correct. Please fix that one too

Yes, it works now, thank you very much.

FYI, Version 7.2 now includes predefined MERGE code formatting template