Forum Discussion

bradwyatt's avatar
bradwyatt
New Contributor II
5 years ago

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

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 t...
error_handling
sqlserver
del's avatar
del
Contributor III
5 years ago

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.