tMSSqlOutput update or insert on data not working

[Anonymous]

Hello I'm reading a delimeted input file and putting that info on SQL Server. Depending on "shop_number" field (that is not PK) from the file, I have to update an existing row or insert a new one into the DB. On my tMSSqlOutput ("Pedido") I selected "update or insert" option on data, and removed the PK field from the schema, I also marked the "shop_number" as key. But when I execute the project, it gets stucked in the red circled part.
I used "sp_who" in the database, and it seems that talend is locking the table and it cant goes on.
whay should I do to get my data into the table? (if im not being clear enough, let me know, so I try to explain it another way)

[janhess]

Changing the key in your map isn't going to get the desired results as your database still thinks it has the same primary key. I think you'll need to use tMSSQLRow to do the update and specify your update query.

[Anonymous]

any particularies on that table, contraints etc ?

I dont believe it does have, because when i tried to use insert only on the output, it inserted just fine. (but leaving it this way will generate multiple rows with the same data)

Changing the key in your map isn't going to get the desired results as your database still thinks it has the same primary key. I think you'll need to use tMSSQLRow to do the update and specify your update query.

that might work, do you know where can I find an example about how to use tMSSQLRow for updates and inserts?

Thank you.

[ytuzet]

with a tMSSQLRow you will have to write by your own the sql update order, I don't think it's the wright way to do what you want
why won't you let insert or update key to id_cliente?

[ytuzet]

if you absolutely want to use check id num_pedido is present in pedido table I think you should separate "cliente" job and "pedido" job
unplug pedido output and transformation from your tmap
create another subjob (a copy) and had to the tmap a lookup with table pedido where join condition is row_a_1.id_pedido=pedido.num_pedido
set back pedido insert or update key to id_cliente

[Anonymous]

with a tMSSQLRow you will have to write by your own the sql update order, I don't think it's the wright way to do what you want
why won't you let insert or update key to id_cliente?

if you absolutely want to use check id num_pedido is present in pedido table I think you should separate "cliente" job and "pedido" job
unplug pedido output and transformation from your tmap
create another subjob (a copy) and had to the tmap a lookup with table pedido where join condition is row_a_1.id_pedido=pedido.num_pedido
set back pedido insert or update key to id_cliente

I dont believe the problem has anything to do with table Cliente or the id_cliente. Looks like when talend check for an existing row in the db, it gets stucked... I will make a test here and insert all clientes first (this part of the job works fine), and then will try to insert pedidos without a lookup into cliente table. Let's see if this changes anything.

[janhess]

Why can't you just specify the correct primary key for pedido. If the record exists it will modify it, if it doesn't exist it will insert it.

[Anonymous]

Why can't you just specify the correct primary key for pedido. If the record exists it will modify it, if it doesn't exist it will insert it.

Because the correct primary key for pedido doesnt come in the input file. If I map that attribute it will always insert a new row into the db, since the PK will be always null. Thats why I'm trying to use 'num_pedido' as key, because that field is present in the input and is unique in my db.

[janhess]

If it doesn't come from the input file how do you expect to match it to your table? And trying to create it your way is always going to produce the null primary key in the output which will be locked after the first attempt at insert/update.

[Anonymous]

If it doesn't come from the input file how do you expect to match it to your table? And trying to create it your way is always going to produce the null primary key in the output which will be locked after the first attempt at insert/update.

good point. I inteded to use the 'num_pedido' field to attempt to update or insert.. Isn't there anyway to do that?

[janhess]

You wouldn't be able to insert without a proper primary key. You can use pedido as a lookup to match the num_pedido to and use the primary key from that for modify or you could use a tMSSQLRow for the update with an update where condition.