MERGE Example - Named Queries

Ignition
1

SQL Server DB

Would someone please give an example of a Named Query using a MERGE statement.

Please show the syntax with multiple On comparisons and multiple columns being updated.

How to use the Ignition Named Query :Parameters

Thank you.

This syntax is not working

MERGE INTO dbo.Users AS target

USING (VALUES (:parm1, :parm2)) AS source (UserId, UserName)

ON target.UserId = source.UserId

WHEN MATCHED THEN

UPDATE SET UserName = source.UserName

WHEN NOT MATCHED THEN

INSERT (UserId, UserName)

VALUES (source.UserId, source.UserName);
2

I haven’t used a merge before but I would typically handle this kind of functionality with a stored procedure.

Stored procedure would take the 2 parameters so your named query would resemble this
EXEC dbo.spName :param1 :param2

Then the stored procedure would resemble this (rough sample)
The text formatting tool got mad about some of the code but it’s a basic create procedure statement with parameters of:
@UserID INT,
@UserName VARCHAR(128)

IF NOT EXISTS (SELECT TOP 1 COUNT(*) FROM tblUsers WHERE UserId = @UserId)
	INSERT INTO tblUsers (UserId, UserName)
	VALUES (@UserId, @UserName)
ELSE
	UPDATE tblUsers
		SET UserName = @UserName
WHERE UserID = @UserId

Edit:
I forgot a where clause on my update. I wrote this in a query window without having any tables and stuff so I didn’t test it. I would recommend testing yours on a test system since it’s using an update statement.

3

I used MERGE to INSERT or UPDATE in named queries at a prior company and they worked great.
I don’t have access to them to provide as a sample.

Validate the query with your database tool (looks like MSSMS for you, SQL Developer, PGAdmin, etc.) or even in the Designer Tools–>Database Query Browser, before you dump it into the Named Query.

Offhand, it looks like the syntax with

is wrong.

4

You were correct, my syntax was invalid. The block below works with the Ignition Named Query with Parameters. Thank you Lynn.

MERGE INTO dbo.table WITH (HOLDLOCK) AS target
USING (Select 
            :Val1 as Val1
           ,:Val2 as Val2
           ,:Val3 as Val3 
           ,:Val4 as Val4
           ,:Val5 as Val5 
           ) as source         
           
ON ( target.Val1= source.Val1
    AND    target.Val2 = source.Val2
    AND    target.Val3 = source.Val3)

WHEN MATCHED THEN
        UPDATE SET 
                    target.Val4= source.Val4,
                    target.Val5 = source.Val5,
                    
WHEN NOT MATCHED THEN                    
    INSERT
            (Val1
           ,Val2
           ,Val3
           ,Val4
           ,Val5
           )
     VALUES
           (:Val1
           ,:Val2
           ,:Val3
           ,:Val4
           ,:Val5
          );