This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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

bradwyatt
New Contributor II

‎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

0 Kudos
1 REPLY 1

del
Contributor III

‎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.

  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.

0 Kudos