You are not explaining very well what you mean by import (whether it is an insert or an update or a little bit of both), so it is hard to suggest a close enough prototype query for you to use directly or with minimal tweaking. However, the key issue, as I understand it, is to convert each nvarchar(10)
value of BOND.MOODYS_RATING
to a corresponding int
key, looking up each string in ValidCreditRatings.CreditRating
and taking the key from ValidCreditRatings.KeyValidCreditRatings
.
That operation takes a simple inner join:
SELECT
b.*,
vcr.KeyValidCreditRatings
FROM
BOND AS b
INNER JOIN Lookup..ValidCreditRatings AS vcr
ON b.MOODYS_RATING = vcr.CreditRating
WHERE
vcr.KeyInstnRatingAgency = 108462
AND vcr.KeyValidCreditRatings <= 21
;
The KeyValidCreditRatings
column in the output of this query will contain the values that you can write to LCDOffering.KeyValidCreditRatingsMoody
.
A join can be used both in an UPDATE
statement and in an INSERT
statement (the INSERT ... SELECT
variation). Use the join in the above example query as a basis for the actual query that will be importing data, adding other filters and/or joins as necessary and putting the vcr.KeyValidCreditRatings
reference where the source for LCDOffering.KeyValidCreditRatingsMoody
should go. By the latter I mean, if for instance, yours is an UPDATE
statement, then you will probably have in the SET
clause an assignment like this:
UPDATE
LCDOffering
SET
...
KeyValidCreditRatingsMoody = vcr.KeyValidCreditRatings,
...
FROM
...
And if it is an INSERT ... SELECT
statement, then you will have vcr.KeyValidCreditRatings
in the select list at the position corresponding to the KeyValidCreditRatingsMoody
column's position in the target column list:
INSERT INTO
LCDOffering (..., KeyValidCreditRatingsMoody, ...)
SELECT
..., vcr.KeyValidCreditRatings, ...
FROM
...