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

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.