MSSQLSERVER MERGE INTO command fails. Needs semi-colon

General Discussion
1

I’m having trouble using MSSQLSERVER (Linux container). I created a change set that includes a MERGE INTO statement, but when I try to run it, Liquibase returns an error message: “A MERGE statement must be terminated by a semi-colon (;).”

My change set looks like this

--liquibase formatted sql

--changeset dtorres:0 runOnChange:true
MERGE INTO Gender AS t
USING (
   VALUES
      (1, 'Male'),
      (2, 'Female'),
      (3, 'Non-Binary'),
      (4, 'Unknown')
) AS s (id, Gender)
ON t.id = s.id
WHEN MATCHED THEN
   UPDATE SET t.Gender = s.Gender
WHEN NOT MATCHED THEN
   INSERT (id, Gender) VALUES (s.id, s.Gender);

This is the error I see:

Running Changeset: changelog/repeatable_changelogs/reference_data/00_gender.sql::0::dtorres
liquibase_1  | 
liquibase_1  | Unexpected error running Liquibase: Migration failed for changeset changelog/repeatable_changelogs/reference_data/00_gender.sql::0::dtorres:
liquibase_1  |      Reason: liquibase.exception.DatabaseException: A MERGE statement must be terminated by a semi-colon (;). [Failed SQL: (10713) MERGE INTO Gender AS t
liquibase_1  | USING (
liquibase_1  |    VALUES
liquibase_1  |       (1, 'Male'),
liquibase_1  |       (2, 'Female'),
liquibase_1  |       (3, 'Non-Binary'),
liquibase_1  |       (4, 'Unknown')
liquibase_1  | ) AS s (id, Gender)
liquibase_1  | ON t.id = s.id
liquibase_1  | WHEN MATCHED THEN
liquibase_1  |    UPDATE SET t.Gender = s.Gender
liquibase_1  | WHEN NOT MATCHED THEN
liquibase_1  |    INSERT (id, Gender) VALUES (s.id, s.Gender)]
liquibase_1  | 
liquibase_1  | For more information, please use the --log-level flag

Here’s a few lines of the debug logs…

Running Changeset: changelog/repeatable_changelogs/reference_data/00_gender.sql::0::dtorres
liquibase_1  | 
liquibase_1  | Unexpected error running Liquibase: Migration failed for changeset changelog/repeatable_changelogs/reference_data/00_gender.sql::0::dtorres:
liquibase_1  |      Reason: liquibase.exception.DatabaseException: A MERGE statement must be terminated by a semi-colon (;). [Failed SQL: (10713) MERGE INTO Gender AS t
liquibase_1  | USING (
liquibase_1  |    VALUES
liquibase_1  |       (1, 'Male'),
liquibase_1  |       (2, 'Female'),
liquibase_1  |       (3, 'Non-Binary'),
liquibase_1  |       (4, 'Unknown')
liquibase_1  | ) AS s (id, Gender)
liquibase_1  | ON t.id = s.id
liquibase_1  | WHEN MATCHED THEN
liquibase_1  |    UPDATE SET t.Gender = s.Gender
liquibase_1  | WHEN NOT MATCHED THEN
liquibase_1  |    INSERT (id, Gender) VALUES (s.id, s.Gender)]
liquibase_1  | 
liquibase_1  | For more information, please use the --log-level flag
2

we are also facing same issue , do you have any solution for this problem.
“Updated Result is Liquibase Community 3.8.5 by Datical”,
16:49:08 ANSIBLE: "Unexpected error running Liquibase: A MERGE statement must be terminated by a semi-colon (;). [Failed SQL: (10713)

3

Hi @bala.yenna and @dtorres-srga,

Our team is currently working on a fix for this issue. You can follow along here For SQL Server, if a delimiter has not been specified, then add one. DAT-13998 by wwillard7800 · Pull Request #4202 · liquibase/liquibase · GitHub

-PJ

1 Like
4

@PJatLiquibase do you have ETA

5

Thanks.

@bala.yenna just so you know I did find a work around for this issue just add the splitStatements:false parameter to your change set and it will work.

(Tested using SQL scripts as changesets, didn’t test this for xml,json,etc.)

1 Like