[thelist] SQL: insert into mutiple tables help

rudy r937 at interlog.com
Tue Apr 10 17:47:47 CDT 2001


> do I need to insert the email, run another query to get
> the ID of the new Subscriber, and yet another SQL
> statement to insert the SubscriberFK to Subscriptions?

hi cory

short answer: in access, yes


>> @@IDENTITY
>
> I think that might do it...unless Access doesn't
> support that SQL function.   Anyone know?

reasonably sure that this is a sql/server feature only

(it's neat though, when you can use it, and it fits the bill exactly)


there are several ways to approach this problem

a trigger would be perfect -- great idea, scott -- but while sql/server and
oracle can do triggers and stored procs, access and mysql can't (as far as
i can recall)

so you are stuck with multiple queries, i.e. insert statements

now this begs the question of how to simulate the @@identity concept

first of all, without getting into the semantics of primary keys, suffice
to say that most people use identity fields (sql/server), sequences
(oracle), autonumbers (access), or auto_increments (mysql)

which leads some people to doing the insert followed by

   select max(id) from thetable

but this can give back the wrong value under circumstances of concurrent
users

<tip>
to discourage yourself from ever being tempted to use select max(id) on an
autonumber, define it random
</tip>

what you have to do is save another field's value in a variable, do the
insert, then run a query to get the autonumber that was assigned

  select id from thetable
     where theotherfield = 'value'

and then use this id as the foreign key in the insert of the child record

works for any autonumber key field, incremental or random, no concurrency
problem

what column to use as the other field?

in your case, cory, i'd suggest email address of subscriber

note that you should probably have a unique constraint on the email column
anyway (otherwise, it's possible to insert two subscribers with the same
email id, and how nice would that be?)

this comes back to the concept that in most cases, the autonumber is a
"surrogate" key that is actually just standing in for the "real" primary
key, which you don't want to make as a primary key for practical purposes
(e.g. if email is the real primary key, using an autonumber means only 4 or
8 bytes in the foreign key, versus the actual email id in the foreign
key -- see, if i put it that way, how absurd it seems?)

so save the new subscriber's email id, insert to the subscriber table, do a
query to get the autonumber that was assigned, then insert the subscription
record(s)

inefficient, but without a stored proc, it's all you can do


rudy.ca







More information about the thelist mailing list