Thread: Question on Multi-Table Inserts

Hello Don,

On 16/04/13 05:59, Don Parris wrote:
> Is it as simple as running a series of insert statements in succession,
> passing on the PK of the first table to the next table in the process?
> I.e.,
>
> BEGIN
> INSERT into table payee(values...)
> INSERT into table transrec(values...) # pass in payee_id variable
> SELECT *from category (to get cat_id)
> INSERT into table transdetails(values...) #pass in transrec_id and
> cat_id variables
> COMMIT
>
> Or is it more complicated than that?

From a business standpoint, and without any further implementation
details, I'd say the first INSERT and the SELECT aren't part of the
logical unit of work, so the database transaction would normally
parallel the financial transaction, i.e., just insert into transrec and
trandetails.

As you said, the payee insert depends on checking for existence, so it
would be conditional.  Similarly, the cat_id has to exist and be found
first.  If this was a typical interactive application, the SELECTs for
payee and category would be done first (before the BEGIN), and the
latter could be done just once with the results cached in the front-end
(shown to the user as a drop-down selection).

Regards,

Joe

On 16/04/2013 11:59, Don Parris wrote:
[snip]
> Is it as simple as running a series of insert statements in succession,
> passing on the PK of the first table to the next table in the process?
> I.e.,
>
> BEGIN
> INSERT into table payee(values...)
> INSERT into table transrec(values...) # pass in payee_id variable
> SELECT *from category (to get cat_id)
> INSERT into table transdetails(values...) #pass in transrec_id and
> cat_id variables
> COMMIT
>
> Or is it more complicated than that?

If your only data store is the database then using a transaction is the
right thing to do. Being all-or-nothing you get exactly what you want.

federico