0

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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.