Multiple Inserts in the Same Stored Procedure

SQL Apprentice Question
I have a project that I have been working on and I need to insert a
record into multiple tables and I if any one of the inserts fails I
need to rollback all of the previous inserts that were done. To
illustrate, I have ten tables that need to have a record inserted into
them and if it errors out on table six, then I want to rollback the
previous five inserts. I'd appreciate any advice I can get. Thanks.

Celko Answers


>> I have a project that I have been working on and I need to insert a record [sic] into multiple tables and I if any one of the inserts fails Ineed to rollback all of the previous inserts that were done. <<


Easy enough; set up a series of INSERT INTO's in a single transaction,
trap each insertion's error and do a ROLLBACK and return if you have a
failure. Do not commit until the end of the whole thing.

The scope of transactions in T-SQL is independent of the block
structure of the language. Think of a "transaction guy" with a bucket
of data looking at a house. The house pumps data into his bucket. He
does not care what is happening inside; he is waiting to see a COMMIT
or ROLLBACK flag come out of the window of the house. At that point,
he either throws the data out or throws it in the database.


But a better question why do you want to store the same data in
multiple tables? The major reason we moved from files to RDBMS was to
get rid of redundancy -- the mantra is "one fact, one time, one way,
one place!" and not "Let's make ten copies and try to keep them all the
same!" Instead of making ten copies of a mag tape with the same data,
we use VIEWs, CTE, and derived tables in SQL.


You did know that a row is not a record in your posting or understand
transactions, makes me wonder if your schema is messed up because you
are mimicing files.