Sql-server – insert data from a column from another table with different data type and the value is based on a lookup table

sql server

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

WHERE:

MOODYS_RATING data type is nvarchar(10) and KeyValidCreditRatingsMoody data type is int(4)
Each value in the column MOODYS_RATING represent a lookup value, being KeyValidCreditRatingsMoody the key for that lookup value
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?

Best Answer

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

Related Solutions

Sql-server – ‘Conversion failed’ error with ORDER BY CASE expression

Your CASE expression can only have one datatype. But yours is mixing datatypes and the highest one is being used (as per datatype precedence). So nvarchar is changed to int based on these rules.

You need a case per datatype or per column

Example:

CASE WHEN @OrderDirection = 'DESC' THEN
                 CASE 
                  WHEN @OrderBy = 'JobNumber' THEN J.JobNumber
                  WHEN @OrderBy = 'CustID' THEN J.CustID
                  WHEN @OrderBy = 'DateIn' THEN J.DateIn
                  WHEN @OrderBy = 'DateDue' THEN J.DateDue
                  WHEN @OrderBy = 'DateOut' THEN J.DateOut
                  WHEN @OrderBy = 'Status' THEN J.MasterJobStatusID 
                  ELSE NULL
                END
            END DESC,
CASE WHEN @OrderDirection = 'DESC' THEN
                 CASE 
                  WHEN @OrderBy = 'OrderNumber' THEN J.OrderNumber
                  ELSE NULL
                END
            END DESC

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

Write your query this way. It includes a better test for integers and dumps ISNUMERIC that returns 1 for '-.', for example.

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
            CONVERT(int, [ZIP5]) END zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM 
        [SpatialData].[dbo].[zip5]
    WHERE
        ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

See this Connect item

SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view or derived table is not materialized can easily be expanded into, from the outer query.

What SQL Server is compiling is really a query that looks like this

SELECT
    CONVERT(int, [ZIP5]) AS zip,
    CONVERT(varchar(max), geom) AS coordinates
FROM 
    [SpatialData].[dbo].[zip5]
WHERE
    ISNUMERIC([ZIP5]) = 1
    AND CONVERT(int, [ZIP5]) >= 85000
    AND CONVERT(int, [ZIP5]) < 86000

You can inspect the query plan of your original, but my guess from looking at the structure is that the WHERE clause uses the expression CONVERT(int, [ZIP5]) twice, so it makes sense to streamline the resolution (calculation to a result) of the expression in the process of retrieving data from the table. This puts the processing of the SELECT clause before the WHERE, so your ISNUMERIC() = 1 never got a chance to filter the bad eggs.

It is by-design.

Best Answer

Related Solutions

Sql-server – ‘Conversion failed’ error with ORDER BY CASE expression

Sql-server – Getting “Conversion failed when converting the nvarchar value to data type int.”

Related Question