MERGE

  • Rick Passaglia

    Grasshopper

    Points: 21

    Could someone try this to see what happens

    I am using SS2K8 - Compatibilty Level 9.0 and am getting the following error when I run the following code:

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near 'MERGE'.

    USE tempdb;

    GO

    DROP TABLE Target

    DROP TABLE Source

    GO

    CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10),

    CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));

    CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10),

    CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));

    GO

    INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');

    INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');

    INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

    GO

    INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');

    INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');

    GO

    USE tempdb;

    GO

    BEGIN TRAN;

    MERGE Target AS T

    USING Source AS S

    ON (T.EmployeeID = S.EmployeeID)

    WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'

    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)

    WHEN MATCHED

    THEN UPDATE SET T.EmployeeName = S.EmployeeName

    WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'

    THEN DELETE

    OUTPUT $action, inserted.*, deleted.*;

    ROLLBACK TRAN;

    GO

  • Gift Peddie

    SSC Guru

    Points: 73570

    You need to change compatibility level to 100 to use MERGE because it was implemented in 2008. Check below for details.

    http://msdn.microsoft.com/en-us/library/bb510625.aspx

    Kind regards,
    Gift Peddie

  • SteveEClarke

    SSCertifiable

    Points: 5161

    Hi,

    I have the same issue - which after taking snipets from online samples and playing them through have been failing - so I have been working through the basics and now know the answer, thank you.

    Just the one question - I have read the MSDN article and just wanted to check what the potential damage could be to an ERP system that is acessing the Database.

    I need to create a procedure that appends records to a table only with the MERGE WHEN NOT MATCHED to TARGET options. Obviously using the compatibility level will allow me to do this - BUT - I do not want to break anything at the same time.

    Is there an alternative "2005" way of doing a merge statement without the merge statement ?

    Basically I want to update a "Snapshot" table at point of Despatch and that only appends records that have not already been added. I suppose an INSERT into which Record = NULL ? loop ?

    Thanks in advance

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Insert into Destination

    from Source where not exists (select from destination where <comparison condition>)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • jacksoncoutinho

    Grasshopper

    Points: 15

    Hi,

    Had a similar query on merge, merge when used on large data takes a very long time to execute. I have data count of over 14 million records, where i am comparing 5 columns data with target table if there is any difference then my merge statement creates a new record if data is matched then merge updates old record.

    Is there any other optimized way to perform this operation besides merge statement. ?

    Any help would be appreciated.

    Thanks,

    -Jack

  • yogesh.l.patil 36576

    SSC Enthusiast

    Points: 105

    HI,
    My question is regarding MERGE from SQL server.
    I am working in SQL 2008.

    I am trying to get the data from One table to 2 temp table with different conditions.
    Then update Quantity using merge 

    but it is giving some syntax error.
    Please find my code snippet.
    -- Inappropriate syntax near the keyword 'INTO'.
    Select * INTO #DT0604_MAX_TARGET from DT0604
    where WID = @strWID
    AND NO = (SELECT MAX(NO) FROM DT0604 where WID = @strWID AND NO <> -1) /*All data less than MAX number in Source table.*/

    Select WID,ACD_FL,DATA_NO,SEQ_NO,GCT_COMP_NO,SUM(QTY) AS QTY INTO #DT0604_MAX_SOURCE from DT0604
    where WID = @strWID
    --AND NO = @str2ndMax_AkajiNo
    /*#DT0604_MAX_TARGET Has max Akaji data.
    Consider all Akaji data in source table except max Akaji data.*/
    AND NOT EXISTS
    (
      SELECT 'X' from #DT0604_MAX_TARGET
      where #DT0604_MAX_TARGET.WID = DT0604.WID
      AND #DT0604_MAX_TARGET.NO = DT0604.NO
    )
    GROUP BY WID,ACD_FL,DATA_NO,SEQ_NO,GCT_COMP_NO

    MERGE INTO #DT0604_MAX_TARGET AS TARGET /* Syntax error here according to SQL*/
    USING #DT0604_MAX_SOURCE AS SOURCE
    ON
    (
      Target.WID = Source.WID AND
      Target.ACD_FL = Source.ACD_FL AND
      Target.DATA_NO = Source.DATA_NO AND
      Target.SEQ_NO = Source.SEQ_NO AND
      Target.GCT_COMP_NO = Source.GCT_COMP_NO
    )

    WHEN MATCHED THEN
    UPDATE SET TARGET.QTY += SOURCE.QTY

    OUTPUT $action,
    DELETED.WID AS Target_WID,
    DELETED.NO AS Target_NO,
    DELETED.ACD_FL AS Target_ACD_FL,
    DELETED.GCT_COMP_NO AS Target_GCT_COMP_NO,
    INSERTED.WID AS Source_WID,
    INSERTED.NO AS Source_NO,
    INSERTED.ACD_FL AS Source_ACD_FL,
    INSERTED.GCT_COMP_NO AS Source_GCT_COMP_NO;

    Select * from #DT0604_MAX_TARGET

    I am not sure where the syntax is going wrong.
    To my surprise if I execute the code in separate parts then it is executed successfully.

    Thank you in advance

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply