Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns93F31A3D90F4Yazorman@127.0.0.1>...
> Jason (JayCallas@hotmail.com) writes:
> > Now to the Exchange_mm_SecurityMaster. I need the individual identity
> > values for each row inserted into SecurityMaster so I can then turn
> > around and insert into Exchange_mm_SecurityMaster. Here are the
> > issues/possibilities as I see it.
> >
> > - @@IDENTITY will not work since I am not inserting a single row at a
> > time
> >
> > - I guess I could INSERT INTO SecurityMaster first, THEN do another
> > INSERT INTO Exchange_mm_SecurityMaster with different where clause.
>
> The dangers of having too many IDENTITY columns.
>

Huh. I am confused. Why is it too many?

I have only 2 IDENTITY columns -- one in SecurityMaster and another in
Exchanges.

The table Exchange_mm_SecurityMaster is for many-to-many entries. The
same security from SecurityMaster table can be on multiple exchanges
from Exchanges table.

> You appear to have a natural key for both tables; use these for the
> connection table too.
>

My problem is not which key I use. My problem is finding the best
approach to inserting many rows at once.

Or are you saying instead of using the IDENTITY columns, from
SecurityMaster and Exchanges, in Exchange_mm_SecurityMaster, use the
SYMBOL and EXCHANGE columns?

> If you really need artificial keys, I would recommened skipping the
> IDENTITY property. Instead take data through a temp table with an
> IDENTITY column. Then determin the highest ID in use in the target
> table, and now you can compute what keys the newly inserted rows
> will have.

Not sure how this would help.

(Just for my own information -- IF I did use the IDENTITY columns,
what would be the best approach to inserting into both tables?)

Thank you for your help in this matter.