Problem with number of deleted records in Snowflake

[DawidZ]

Hello,

I have an issue in Talend Studio getting the number of deleted records in the Snowflake database.

To delete records from a table I’m using tSnowflakeRow component, 

but when I’m trying to read data from NB_LINE property then I’m getting “null” value.

I also tried to use the Row -> Main link and pass the records returned by tSnowflakeRow to another component,

but in the case of the DELETE command, 0 lines are returned, despite the fact that in Snowflake itself a row with the number of deleted records is returned.

The weird thing is that when I call a stored procedure in the tSnowflakeRow component that returns a value, I get 1 row with that value, and if I call delete, I don't.

Is it possible to get the number of records deleted in Snowflake?

Should I use a different component?

If Yes, which one?

Best Regards,

Dawid

[gjeremy1617088143]

Hi, you could do this in two times : first you use tdbInput to select the id of the records of the table with your condition on the country then you link it with a tdboutput with a delete action, tDBoutput have a globalVar: Number of success.(in the schema of tDboutput you have to check key for your id column).

Send me Love and Kudos

[DawidZ]

Hi gjeremy,

Using tDBInput or tDBRow component to execute a select statement to get the count of record is wrong due to the below reasons:

- additional time

- additional costs

- performance of the solution

Is there no other way to read the number of deleted records?

The Snowflake database itself returns the “number of rows deleted” after the DELETE operation.

Best Regards,

Dawaid

[gjeremy1617088143]

Hi maybe you could use :

https://docs.snowflake.com/en/sql-reference/functions/result_scan.html

if you can do multi statement in the dbrow.

[DawidZ]

Hi, unfortunatelly it's not working...

I can't do multi statement in the tSnowflakeRow component 😞

Best Regards,

Dawid

[gjeremy1617088143 · Accepted solution]

you could also use stored procedure to combine multiple statement and return what you want.

[DawidZ]

Thanks a lot, stored procedure is working.

Maybe it's not a pretty solution, but it's better than selecting the data before deleting it.

[gjeremy1617088143]

Please select a best answer, it's important to reward the community wich take time to respond to your answer.

Send me Love and Kudos