How to exclude a field in update

One Star

How to exclude a field in update

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.
Employee

Re: How to exclude a field in update

If the column is a part of the primary key, it should be set as "Key" in TOS schema and it won't be updated by the update query (but used in the where clause)
One Star

Re: How to exclude a field in update

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.
One Star

Re: How to exclude a field in update

I 've the same problem :'( 1077
My solution causes bad performances. This is a Talend's disadvantage.
One Star

Re: How to exclude a field in update

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.
One Star

Re: How to exclude a field in update

Is someone has a solution for this issue ?
One Star

Re: How to exclude a field in update

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
One Star

Re: How to exclude a field in update

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.
Employee

Re: How to exclude a field in update

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".