Search code examples
sql-servertalend

Talend Truncate Table does not empty table

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

Thanks in advance

Solution

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