In a tMSSQLOutput (but it's the same for all DBOutput component), i would like to exclude a field only in the update mode but not for the insert. Typically, it's a field that is necessary for the insert mode (a part of the primary key : primary key is defined on 2 fields) but i can't update it (the database don't allow that). I have a tMSSQLOutput and I've choosen Insert or Update. I want to use another key for the update (a functional key) and the technical key for the insert. If i tried to change the key option in the tMSSQLOutput i have this error : Program : Changes on sqlId are not allowed. Transaction rolled back.
Yes but in many situation it is very important to make update on other key than the primary key. In my case the primary key is compose of 2 field (an IDENTITY field and another int field). CREATE TABLE .( IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, NOT NULL, (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT PRIMARY KEY ( ASC, ASC ) When I insert data in this table I only set the ProjectSqlId in the tMap (not the ProjectIncId because IDENTITY) but I want to update on the Alternative Key (AK) ProjectCode that is unique in the table. For example for a job only in update, it is interesting to set the update key on a AK instead of the primary key that sometimes is much more a technical key than a true business key.
I have a look at your solution and it is exactly what I'm thinking about to find a way;nevertheless it is not good. Moreover Talend has all the features to do this stuff. I will post a Bugtracker as feature to add this crucial functionality.
I think that, using only your tMSSQLOutput is difficult to achieve that. In this particular case, you could use a tMap before the tMSSQLOutput to discriminate the insert and the update cause the problem is not talend but the database that don't allow to update the column regards
Yes if i use a tMap to discriminate the insert and the update, i have to add my target database also as source table and make inner join on it to discriminate. Very bad for performance ! But nevertheless probably this is the unique solution waiting for my feature request is being added in the next release.
As you can read on 2063, I've just added a "Field options" property on tMysqlOutput (other DBMS will come once validated on MySQL). Now you can choose which fields are part of the update key, the delete key, are "insertable" and "updatable".