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/techinf...2000/books.asp