SQL: How use case and cast in a query?
You might just want to solve this in general and deal with any non-int value the same way
INSERT INTO labbd11..movie(title, year) SELECT movies.title, CASE WHEN IsNumeric(movies.mvyear+ '.0e0') <> 1 THEN NULL ELSE CAST (movies.mvyear AS INT) END FROM disciplinabd..movies
See this question
I believe you would want something like
INSERT INTO labbd11..movie(title, year) SELECT movies.title, CAST( CASE movies.mvyear WHEN '????' THEN NULL ELSE movies.mvyear END AS INT) FROM disciplinabd..movies
You want your CASE statement to return a VARCHAR (either the MVYEAR
or NULL) and then you want the CAST to operate on the result of the CASE.
INSERT INTO labbd11..movie(title, year) SELECT movies.title, CASE WHEN movies.mvyear = '????' THEN NULL ELSE CAST (movies.mvyear AS INT) END FROM disciplinabd..movies