How to Insert All Records but if there's an error then insert none
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-08-2021 05:14 PM
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-12-2021 01:32 PM
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.
-
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.
-
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.
