I need to insert data from a column from another table with different data type and the value is based on a lookup table

Question

I need to import data taken from table_1.column_1 (BOND.MOODYS_RATING) to table_2.column_2 (LCDOffering.KeyValidCreditRatingsMoody)

WHERE:

1. MOODYS_RATING data type is nvarchar(10) and KeyValidCreditRatingsMoody data type is int(4)
2. Each value in the column MOODYS_RATING represent a lookup value, being KeyValidCreditRatingsMoody the key for that lookup value

3. KeyValidCreditRatingsMoody values are equal to KeyValidCreditRatings, taken from the table ValidCreditRatings, while MOODYS_RATING values are equal to the field CreditRating, also from the table ValidCreditRatings, subject to certain restrictions, which can be summarized in this query:

   select * from Lookup..ValidCreditRatings
   where KeyInstnRatingAgency = 108462 
   and KeyValidCreditRatings <=21 order by keyvalidcreditratings
   

Can you please help me build an insert statement that can fulfill all of these requirements?

Answer 1

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