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?
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.
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.
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.)