MERGE

• Rick Passaglia

  Valued Member

  Points: 51

  More actions

  April 15, 2009 at 9:07 am

  #95124

  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

  More actions

  April 15, 2009 at 9:24 am

  #978173

  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

  More actions

  September 26, 2011 at 5:13 am

  #1386370

  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: 1004504

  More actions

  September 26, 2011 at 5:30 am

  #1386386

  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

  More actions

  September 19, 2014 at 9:58 am

  #1746771

  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 Veteran

  Points: 275

  More actions

  December 5, 2018 at 11:23 pm

  #2015627

  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