none
MERGE statement failing RRS feed

  • Question

  • You should try to avoid loops at all. The power of SQL is in set based operations. If you post here your table structure, sample data, and the logic, then someone can assist you with finding a good method.

    If you are on SQL Server 2008 then take a look at the MERGE statement:
    http://technet.microsoft.com/en-us/library/bb510625.aspx
    Plamen Ratchev


    Hi,

    I have comaptability level of 90 for SQL Server 2008. According to Microsoft MERGE should work with this.

    However, when I execute my stored proc I get the below error...

    Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

    Why is this?

    Thanks.

    Thursday, June 16, 2011 2:09 PM

Answers

  • Did you try ending the statement, previous to MERGE, with semicolon (;)?

    INSERT INTO @Temp
      (
    Type,
    UserID
      )
      SELECT n.Type, n.UserID
      FROM Reports n
      INNER JOIN ReportSets rs
      ON rs.ID = n.ID
      WHERE rs. ID = @ID; -- <<<<<<<<<<<<<<
      
      -- update temp table level field
      MERGE @Temp AS T
      USING Categorisation AS C
      ON (T.Type = C.Description)
      WHEN MATCHED
    THEN UPDATE SET T.Level = C.DefaultLevel;

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by obrienkev Monday, June 20, 2011 4:20 PM
    Monday, June 20, 2011 3:42 PM
    Moderator
  • As suggested in this thread, add semicolon right before merge, e.g.

    TRY

     ;MERGE ....


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, February 4, 2013 10:31 PM
    Moderator

All replies

  • Are you sure you're using SQL 2008? What does SELECT @@Version return?
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, June 16, 2011 10:38 PM
    Moderator
  • Are you sure you're using SQL 2008? What does SELECT @@Version return?
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    This...

    Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)   Sep 16 2010 19:43:16   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2) (VM)

    Friday, June 17, 2011 10:05 AM
  • I have SQL Server 2008 R2 and switching to compatibility mode 90 and running merge command works for me.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, June 17, 2011 1:11 PM
    Moderator
  • I have SQL Server 2008 R2 and switching to compatibility mode 90 and running merge command works for me.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Any ideas why this does not work for me? I right-click on Database, select Properties > Options, and Compatability Level is set to 'SQL Server 2005 (90)'

    Do I need to change this to SQL Server 2008 (100)? And if so could this cause problems with existing tables and stored procedures?

    Or is it something with my code?

      INSERT INTO @Temp
      (
    		Type,
    		UserID
      )
      SELECT n.Type, n.UserID 
      FROM Reports n
      INNER JOIN ReportSets rs
      ON rs.ID = n.ID
      WHERE rs. ID = @ID  
      
      -- update temp table level field
      MERGE @Temp AS T
      USING Categorisation AS C
      ON (T.Type = C.Description)
      WHEN MATCHED
    		THEN UPDATE SET T.Level = C.DefaultLevel;
    

    Thanks.


    • Edited by obrienkev Monday, June 20, 2011 9:10 AM
    Monday, June 20, 2011 8:40 AM
  • Hi,

    I think changing the compatibility level to 90 means SQL server 2005 and merge is not supported with sql server 2005


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Monday, June 20, 2011 9:07 AM
  • Try dropping T.Level and use just Level in the MERGE. If it still will not work, then probably you can open a case with MS. The documentation clearly states that MERGE is supposed to work with compatibility mode 90 and I confirmed this with a test (although I tested with SQL 2008 R2, not just SQL 2008). Also make sure you're on the latest SP for SQL Server.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, June 20, 2011 1:37 PM
    Moderator
  • Try dropping T.Level and use just Level in the MERGE. If it still will not work, then probably you can open a case with MS. The documentation clearly states that MERGE is supposed to work with compatibility mode 90 and I confirmed this with a test (although I tested with SQL 2008 R2, not just SQL 2008). Also make sure you're on the latest SP for SQL Server.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Tried the above. No change.

    SQL Server 2008 is also up to date.

    Monday, June 20, 2011 2:50 PM
  • Did you try ending the statement, previous to MERGE, with semicolon (;)?

    INSERT INTO @Temp
      (
    Type,
    UserID
      )
      SELECT n.Type, n.UserID
      FROM Reports n
      INNER JOIN ReportSets rs
      ON rs.ID = n.ID
      WHERE rs. ID = @ID; -- <<<<<<<<<<<<<<
      
      -- update temp table level field
      MERGE @Temp AS T
      USING Categorisation AS C
      ON (T.Type = C.Description)
      WHEN MATCHED
    THEN UPDATE SET T.Level = C.DefaultLevel;

     


    AMB

    Some guidelines for posting questions...

    • Marked as answer by obrienkev Monday, June 20, 2011 4:20 PM
    Monday, June 20, 2011 3:42 PM
    Moderator
  • Did you try ending the statement, previous to MERGE, with semicolon (;)?

    INSERT INTO @Temp
      (
    Type,
    UserID
      )
      SELECT n.Type, n.UserID
      FROM Reports n
      INNER JOIN ReportSets rs
      ON rs.ID = n.ID
      WHERE rs. ID = @ID; -- <<<<<<<<<<<<<<
      
      -- update temp table level field
      MERGE @Temp AS T
      USING Categorisation AS C
      ON (T.Type = C.Description)
      WHEN MATCHED
    THEN UPDATE SET T.Level = C.DefaultLevel;

     


    AMB

    Some guidelines for posting questions...


    That's it!! Thanks!!
    Monday, June 20, 2011 4:20 PM
  • The explanation is in BOL ( MERGE (Transact-SQL) ).

    *****

    MERGE is a fully reserved keyword when the database compatibility level is set to 100. The MERGE statement is available under both 90 and 100 database compatibility levels; however the keyword is not fully reserved when the database compatibility level is set to 90.


    *****

    I think It is time, for us, to get use to end each statement with semicolon.

     


    AMB

    Some guidelines for posting questions...


    Monday, June 20, 2011 5:17 PM
    Moderator
  • I'm not sure how the above exert from the Help translates into ending the statement with semicolon. Although, it's a good habit to start getting into.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, June 20, 2011 5:20 PM
    Moderator
  • I have SQL Server 2008 R2 and switching to compatibility mode 90 and running merge command works for me.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Hello Naomi,

    The MERGE stmt didn't work for me on DB with compatibility level of 90; but works fine on DB with 100 on same server - wondering if I m missing anything?

    Before reading your post, I was assuming MERGE will not work on 90.

    Thanks

    Mahesh

    Friday, January 25, 2013 8:10 PM
  • Did you end your previous statement with semicolon?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, January 25, 2013 9:00 PM
    Moderator
  • MERGE statement will work in a database with CL 90, but the db should reside in a SS 2008 instance or greater.

    This script will work fine in a SS 2008 R2 instance with the db in CL 90 (AdventureWorks2008R2).

    DECLARE @T TABLE (c1 int);
    DECLARE @S TABLE (c1 int);
    
    INSERT INTO @S (c1) VALUES (1);
    
    MERGE INTO @T AS T
    USING @S AS S
    ON 1 = 0
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (c1) VALUES (S.c1);
    
    SELECT * FROM @T;
    SELECT * FROM @S;
    
    SELECT SalesOrderID AS MERGE
    FROM Sales.SalesOrderHeader;
    GO

    The same script will fail if you change the CL to 100, because the word MERGE is a fully reserved word in that CL. To make it work, you have to enclose it between brackets.

    SELECT SalesOrderID AS [MERGE]
    FROM Sales.SalesOrderHeader;


    AMB

    Some guidelines for posting questions...



    Friday, January 25, 2013 9:14 PM
    Moderator
  • However, this script does not work in CL90:

    DECLARE @T TABLE (c1 int)
    DECLARE @S TABLE (c1 int)

    INSERT INTO @S (c1) VALUES (1)

    MERGE INTO @T AS T
    USING @S AS S
    ON 1 = 0
    WHEN NOT MATCHED BY TARGET THEN
    INSERT (c1) VALUES (S.c1);

    SELECT * FROM @T
    SELECT * FROM @S

    That is, in CL90 you must terminate the previous statement with a semicolon. This is not required in CL100 and higher.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 25, 2013 11:16 PM
  • Sorry for my late response.  

    In my case I was using MERGE within TRY CATCH which errors under the CL90 otherwise it works:

    Msg 325, Level 15, State 1, Line 8
    Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.
    Msg 156, Level 15, State 1, Line 9
    Incorrect syntax near the keyword 'AS'.

    Code I tested:

    DECLARE @source TABLE (   [id] INT PRIMARY KEY,  [name] VARCHAR(10));
    INSERT @source VALUES(1000,'Harold'),(2000,'Madge');
    DECLARE @destination TABLE (   [id] INT PRIMARY KEY IDENTITY(1,1),  NAME VARCHAR(10));
    
    
    BEGIN TRY 
    MERGE  @destination
    USING  (SELECT [id], [name] FROM @source) AS [source]
    ON     (1=0) --arbitrary join condition
    WHEN   NOT MATCHED THEN
           INSERT (name)
           VALUES  (source.Name)
           OUTPUT  INSERTED.id AS NEWID,[source].[id] AS OldId,INSERTED.name;
           
    END TRY 
    BEGIN CATCH
    print 'ERROR'
    END CATCH

     

    so the workaround for me was:

    Since my Master Db was on CL100, I change the DB context to :

    USE Master

    GO

    and rest of my script (run MERGE) using Database qualifier like: myCL90DB.dbo.table..

    Thanks



    Mahesh



    • Edited by Mahesh Dasari Monday, February 4, 2013 10:06 PM added workaround
    Monday, February 4, 2013 9:43 PM
  • As suggested in this thread, add semicolon right before merge, e.g.

    TRY

     ;MERGE ....


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, February 4, 2013 10:31 PM
    Moderator
  • As suggested in this thread, add semicolon right before merge, e.g.

    TRY

     ;MERGE ....


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thanks Naomi, that worked! I missed to read terminating the preceding statement with semicolon. I only thought we need to terminate the MERGE stmt with semicolon;  - to put it simply, we need to begin and end MERGE stmt with semicolon..  :)


    Mahesh

    Tuesday, February 5, 2013 2:00 PM
  • As suggested in this thread, add semicolon right before merge, e.g.

    TRY

     ;MERGE ....


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thanks Naomi, that worked! I missed to read terminating the preceding statement with semicolon. I only thought we need to terminate the MERGE stmt with semicolon;  - to put it simply, we need to begin and end MERGE stmt with semicolon..  :)


    Mahesh


    Mahesh, please mark Noami's reply as the answer. Thanks!

    Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, February 7, 2013 4:42 AM
    Owner