How to Insert All Records but if there's an error then insert none

Designing Pipelines
,
#1

Hello,

If I have 3 records that I want to insert into my database table, I use the “SQL Server - Insert” snap. However, if there’s 1 record that has an error (like let’s say one of the values has text in an int column), the 2 other records still continue getting inserted into the table.

I want it to be the case where either all 3 records are inserted into the table, or if at least 1 record gets an error then none of the records get inserted.

Thanks

#2

Hi @bradywyatt,

I don’t think you can accomplish what you want with the SQL Server - Insert snap as you stated, but I have a couple of ideas that may help.

  1. We architect our SQL Server Datamart/ETL pipelines where they load the data, using the SQL Server - Insert snap, into a “staging” table. Then, upon success of the entire load, the pipeline fires a stored procedure, using the SQL Server - Stored Procedure snap, which loads the data from staging to the production table. Upon error, the data is not moved to production.

  2. I’ve never tried the following approach using SnapLogic, but I you might be able to design your pipeline to use the SQL Server - Execute snap to utilize T-SQL transactions. Again, I haven’t tried this using SnapLogic, so I’m not sure if it’s a viable option.