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.