db merge insight

Need a bit of insight on db merge node.

Say I have a table in sql server t1. I has one field called f1 which is a string and does not allow nulls with c1 being the primary key it also has a second field f2 which is a string.

Now I want to add data to the table updating where primary key matches and inserting if it does not.

I believe db merge is the node to use. But I’m confused how to get it to work. I have connected db merger on the input side to a sql server connector and the table used that will be updating table on server.

On configuration of db merge I select the table that needs to be updated/inserted to.

In the (SET in SQL) I tell it what fields will be updated (f1 & f2).
In the (WHERE in SQL) I give it the primary key f1 (which is both in table with new data and the table to be updated/inserted).

The node never allows me to execute so I think I do not have it configured properly.

Questions: a) Is db merge the node to use to insert/update data in a sql server table?
b) if so might there be any insight on why I can’t get it to execute?

Thanks,
Jeff

Oops… looks like I had it misconfigured slightly. Apparently (SET in SQL) cannot include same fields as (WHERE in SQL). Once configured properly it works.

1 Like

Glad you figured it out - thanks for posting your solution.

Back to not getting db merge to work correctly… something I’m missing.

I have a table _mytesttable with two fields pk (which is primary key), and c1 (data field).

I can use db merge and insert the following data:
pk c1
a a1
b b1
c c1

that works good.

Then I take a new row and want to merge it (which to me means update if pk exists update otherwise insert.

if I try to use the table
pk c1
a xx

when I execute db merge it says I’m trying to insert a duplicate. I get the error:
ERROR DB Merge 0:3 Execute failed: Violation of PRIMARY KEY constraint ‘PK_mytesttable’. Cannot insert duplicate key in object ‘dbo._mytesttable’. The duplicate key value is (a).

I check configuration and in (Set in SQL) I have pk and in (WHERE in SQL) I have c1.

I am surely missing something simple but can’t figure it out.

Ideas appreciated.

I think you just have the SET and WHERE criteria reversed. Use SET = c1 and WHERE = pk in the configuration dialog and see if that works. (In an example I just threw together on my laptop, it does.)

1 Like

Ugh… so simple yet I could not see it… Thanks so much!
Jeff

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.