Talend : Why truncate a table does not empty table

Question :

I use TOS to transfer a SQL Server table to another SQL Server. That works more or less. But I have one issue with truncating the table. In the properties for the output table I define “Truncate Table” for the table action and “Insert” for the data action. At the second run I get a lot of duplicate key errors. If I run the “TRUNCATE TABLE” manually in the SQL Server Management Studio, the job works fine.

Are there any known issues with truncate table? Talens Version is 5.3.2

Answer : 

I mimicked the scenario and it works fine in Talend Platform for data Management version 5.6.1. I cannot test it on the TOS, but perhaps you can upgrade to the newest TOS version and try again. To be thorough I tried it using separate connection components and built-in connections. The only difference is that using separate connection object requires a commit object.

The workaround I recommend is this:

  • create a proc to truncate your table and call it from a tMSSqlSP component
  • connect this to your original subjob which transfers the data between the two tables using an OnSubJobOK flow.
  • In your tMSSqlOutput component (which performs the truncate/insert) in for Action on Table use Default (so it will not truncate the table)
  • for Action on data use Insert

I tried this method and it works. This workaround will save you the time and frustration of dealing with the TOS issue.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.