I am using a Database Writer node to save 50,000 records into a Teradata database. I went to the advanced tab of that node and I specied a batch size of 1,000. When I run the workflow, it inserts most of the 50,000 rows into the database, but it fails to insert 150 rows because of table constraints.
Is there some way to specify an "all or nothing" when running this workflow? In other words, does the Database Writer node allow me to specify a database transaction to ensure that either all rows go in or no rows go in?
unfortunately there is no way right now that allows you to do so. Currently the Database Writer node tries to write as many rows into the database as possible even if an error occurs the other rows are written into the database.
We see your point that this isn't a desired behavior in all cases and will add an all or nothing option to the Database Writer node with one of the next releases. If the option is selected the node will fail if an error occurs in which case no rows will be written to the database.