CODEWITHSUNDEEP
sqlsql-server
Abhijeet
Abhijeet

Reputation: 13856

SQL Server : Bulk Insert in Transaction

Inserting bulk of records in a transaction.

BEGIN TRANSACTION DATAINSERT

-- INSERT QUERIES HERE

COMMIT TRANSACTION DATAINSERT

But even though scripts in the middle of file encountered foreign key constraints, previous inserts were not rolled back.

The INSERT statement conflicted with the FOREIGN KEY constraint

All I want is scripts should succeed if and only if all inserts are successful without any constraint violations.

Upvotes: 0

Views: 629

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294177

Not all errors imply rollback. It depends on severity. Sometimes you need to explicitly rollback in case of error.

Set SET XACT_ABORT ON to force all errors to be transaction aborting ones, but be wary that your script does not continue expecting a transaction and auto-committing each statement after the error.

Upvotes: 2

Related Questions