Complicated (at least to me) insert

The best way to explain this is by example.

I have a source table with many columns.

Source
SYMBOL
EXCHANGE_NAME
CUSIP
TYPE
ISSUE_NAME
and so on

Then I have 3 other destination tables.

Exchanges
EXCHANGE_ID IDENTITY
EXCHANGE_NAME UNIQUE

SecurityMaster
SECURITY_MASTER_ID IDENTITY
SYMBOL UNIQUE
CUSIP
TYPE
ISSUE_NAME
and so on

Exchange_mm_SecurityMaster
EXCHANGE_ID
SECURITY_MASTER_ID

-- The Source table has multiple rows of the same symbol.
-- The Exchanges table is already populated with all the exchanges.
-- A single security (in the SecurityMaster table) can belong to many
Exchanges, hence the Exchange_mm_SecurityMaster table.

Now. If I just wanted to insert into the SecurityMaster table without
touching the Exchange_mm_SecurityMaster table I could just execute:

INSERT INTO SecurityMaster ([SYMBOL], [CUSIP], [TYPE], [ISSUE_NAME])
SELECT DISTINCT[SYMBOL], [CUSIP], [TYPE], [ISSUE_NAME]
FROM Source
WHERE NOT EXISTS (SELECT * FROM SecurityMaster SM WHERE SM.SYMBOL =
Source.SYMBOL)

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.

- I could create a stored procedure that does a single insert into
SecurityMaster and Exchange_mm_SecurityMaster. Then call that
procedure for each row in the SELECT DISTRICT from the Source table.
My main worry is the number of arguments passed in. My example only
shows a few but a regular SecurityMster table could have 30-50
columns.

- Maybe do something with a trigger but I am not sure if I can pass
the EXCHANGE_NAME value to the SecurityMaster trigger when that table
does not need it.

Hope I explained it clearly. Any help would be appreciated.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.

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

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.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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.|||Jason (JayCallas@.hotmail.com) writes:
> Huh. I am confused. Why is it too many?
> I have only 2 IDENTITY columns -- one in SecurityMaster and another in
> Exchanges.

Since both tables appears to have natural one-column keys, I am not
convinced that using IDENTITY is called for.

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

Yes.

>> 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.

As I understood it, problem is that you say:

INSERT tbl_a (...)
SELECT ...
FROM src

INSERT tbl_b (...)
SELECT ...
FROM src

And now you are to insert into the relation table, but you don't know
what the keys are.

But since the natural keys come from the src, you could say:

INSERT tbl_c (a_ident, b_ident)
SELECT a.a_ident, b_ident
FROM src s
JOIN tbl_a ON a.a_narural_key = s.a_natural_key
JOIN tbl_b ON b.b_narural_key = s.b_natural_key

Provided that you have all information available. Since your post
only included sketches of what you are doing, it is difficult to
tell if this is entirely applicable.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp