Hi,
I am working with MS SQL Server and try to perform a MERGE operation in the database. The SQL statement roughly looks like:
MERGE [TableTarget] AS tgt
USING [TableSource] AS src
ON (tgt.[A] = src.[A] AND tgt.[B] = src.[B])
WHEN MATCHED THEN
UPDATE SET
tgt.[C] = src.[C]
,tgt.[D] = src.[D]
WHEN NOT MATCHED THEN
INSERT ( [A], [B], [C], [D] )
VALUES ( src.[A] ,src.[B] ,src.[C],src.[D] )
OUTPUT inserted.[D];
I am using the “DB Query Reader” node for this in order to retrieve back the data specified by the OUTPUT clause.
When I execute the above statement in a DB Management system (e.g. MS SQL Server Management Studio), it works perfectly. Doing the INSERT or UPDATES, and returning the data requested in OUTPUT.
However, when I execute it in the DB Query Reader node, it doesn’t do any INSERT or UPDATE. (But it still returns data…)
Any ideas how this is possible and why it doesn’t do any insert/update?
Hello @atothep ,
I don’t think that it is possible to execute a DML statement and return data at the same time with the nodes that we have. Could you maybe store the output value(s) in a temporary table which you query once the merge statement is done?
You can execute multiple statements in the DB SQL Executor when you enable the “Support multiple SQL statements” options. I have found an example which might work here:
DECLARE @OutputTbl TABLE (ID INT);
INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID INTO @OutputTbl(ID)
VALUES ('Yatrix', '1234 Address Stuff', '1112223333');
That’s too bad. In fact, I was using a temporary table very similar to your proposal. But then I realized that, in principle, I don’t need it and can return the IDs right away… Maybe now I have to go back to the temporary table approach.
Just out of curiosity: Can you explain why the DB Query Reader doesn’t actually insert anything into the table? I mean, obviously, the statement is executed. And for some reason I even get back the inserted IDs. But as a matter of fact, no data is inserted or updated in the table. How can that be?