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.

1 Like
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.