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.