Merge Syntax SQL Server 2008

I am basically trying to do the thing from MYSQL where it is INSERT and ON DUPLICATE, etc.

How开发者_如何学Goever, I am getting an error with my 'merge' syntax. This stored procedure takes a variable "@ID" which is a guid and looks in the table to see if it exists already -- if it does, insert a new entry, otherwise update the existing. I can't figure out what is wrong here!

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE usp_DigitalTool_InsertUpdate2
(@ID uniqueidentifier, @TOOL_ID uniqueidentifier, @INPUT_ID uniqueidentifier)

AS

MERGE 
INTO  DigitalTool as target
USING (SElECT @ID,@TOOL_ID,@INPUT_ID) as source(id, tool_id, input_id) 
ON (target.ID = source.id)
when matched then  
 update
  set TOOL_ID = source.tool_id,
    INPUT_ID = source.input_id
when not matched then
insert ( ID, TOOL_ID, INPUT_ID)
    values ( NEWID(), source.tool_id, source.input_id)

END
GO

Help is appreciated. Thanks!

---

You need something like

    MERGE 
INTO  DigitalTool as target
USING (SElECT @ID,@TOOL_ID,@INPUT_ID) as source(id, tool_id, input_id) 
ON (target.id = source.id)
when matched then  
 update
  set TOOL_ID = source.tool_id,
    INPUT_ID = source.input_id
when not matched then
insert ( ID, TOOL_ID, INPUT_ID)
    values ( NEWID(), source.tool_id, source.input_id)

Update.

There are couple of mistakes in your procedure (BEGIN missed, as well as semicolon at the end). It should look:

CREATE PROCEDURE usp_DigitalTool_InsertUpdate2
(@ID uniqueidentifier, @TOOL_ID uniqueidentifier, @INPUT_ID uniqueidentifier)  
AS
BEGIN 
 MERGE 
 INTO  DigitalTool as target
 USING (SElECT @ID,@TOOL_ID,@INPUT_ID) as source(id, tool_id, input_id) 
 ON (target.ID = source.id)
 when matched then  
 update
 set TOOL_ID = source.tool_id,
   INPUT_ID = source.input_id
 when not matched then
 insert ( ID, TOOL_ID, INPUT_ID)
  values ( NEWID(), source.tool_id, source.input_id);
END;

---

Don't you need the USING clause?

See MERGE.

---

Using TSQL You can use the EXISTS keyword to determine whether a record is found as follows.

IF EXISTS(
 SELECT 1
 FROM MY_TABLE
 WHERE ITEM='somevalue' AND ENTERDATE='12/31/1999')
    --Update Statement
    UPDATE MY_TABLE
    SET ITEM='anothervalue'
    WHERE ITEM='somevalue' AND ENTERDATE='12/31/1999'
ELSE
    --Insert Statement
    INSERT INTO MY_TABLE
    (ITEM, ENTERDATE)
    VALUES
    ('somevalue', '12/31/1999')

---

merge DigitalTool 
using (select ID from Table_name) as tbl
ON tbl.ID = @ID
when matched then
    update
    set TOOL_ID = '@TOOL_ID',
        INPUT_ID = '@INPUT_ID'
when not matched then
    insert ( ID, TOOL_ID, INPUT_ID)
        values ( NEWID(), @TOOL_ID, @INPUT_ID )

---

You have one END to much and the MERGE needs to be terminated with ;

CREATE PROCEDURE usp_DigitalTool_InsertUpdate2
(@ID uniqueidentifier, @TOOL_ID uniqueidentifier, @INPUT_ID uniqueidentifier)

AS

MERGE 
INTO  DigitalTool as target
USING (SElECT @ID,@TOOL_ID,@INPUT_ID) as source(id, tool_id, input_id) 
ON (target.ID = source.id)
when matched then  
 update
  set TOOL_ID = source.tool_id,
    INPUT_ID = source.input_id
when not matched then
insert ( ID, TOOL_ID, INPUT_ID)
    values ( NEWID(), source.tool_id, source.input_id);