Semi-colon insertion problem with MERGE

schematech

We have some valid, working code that does not go through the Ctrl-B,C process. Here is a snippet of the affected statement, from within a much larger sproc that I cannot post here:

merge CardPoints as target
		using CardpointsNew AS source
		on (target.Card = source.Card)
		when matched and target.Balance <> source.Balance then
			update set target.Balance = source.Balance
		when not matched by target then
			insert (Card, Balance)
				values (source.Card, source.Balance)
		when not matched by source then
			delete
		output $action, deleted.Card as OldCard, deleted.Balance as OldBalance, inserted.Card as NewCard, inserted.Balance as NewBalance
			into CardMergeActions (Action, OldCard, OldBalance, NewCard, NewBalance);

When I try to do the semi-colon insert it says it cannot complete the operation, and it highlights the column name 'Action' in the last line with a red squiggle, which when you hover over it says "Incorrect syntax near Action". This is a valid column name in the CardMergeActions table, and as I said, the code works. It isn't shown as a syntax error except when you try to do semi-colon inserts. It does not help putting square brackets around [Action], either. So, it seems to be detecting a syntax error where none exists.

Another oddity - the MERGE, as shown above, already has a semi-colon terminator (but much code around it does not). If I remove that semi-colon, and do Ctrl-B,C, it puts a squiggle at the end of the line as well, and it announces that "a merge statement must be terminated by a semi-colon"! Well, yes - isn't that what I'm asking it to do? Does that mean your function will only insert semi-colons where they are otherwise (currently) optional?

Regards,
Bob

Aaron L

Hi Bob,

We can recreate this here and we're currently investigating. It looks like there might be a bug in the way we're handling the column list on the output clause (removing (Action, OldCard, OldBalance, NewCard, NewBalance) seems to get rid of this error message) we'll continue to look into this and let you know when we have a fix.

Thanks,
Aaron.

Aaron L

It looks like we did have an error in our parser that was causing this, I've got a new private build for you here which should have a fix.

Thanks for reporting all these issues, it really helps make the software much more stable.

schematech

Many thanks, Aaron, for your prompt reply and super-quick fix!
I have installed the build and it addresses both issues I raised.

Cheers,

Bob