Conversion failed when converting the varchar value 'SAT' to data type int

0
votes

I have a table ConsoleGames wherein all columns are of type varchar(50). When I try to create a new table console_games by amending existing datatypes by using the query:

CREATE TABLE console_games
(
    game_rank integer,
    game_name varchar(1200),
    platform_name varchar(1200),
    game_year integer,
    genre varchar(200),
    publisher varchar(1200),
    na_sales float,
    eu_sales float,
    jp_sales float,
    other_sales float
)

INSERT INTO console_games
    SELECT * 
    FROM [dbo].[RAWConsoleGames]

I get the following error message:

Msg 245, Level 16, State 1, Line 17
Conversion failed when converting the varchar value 'SAT' to data type int.

When I look into the data in the table the value 'SAT' is in a column for which I am not changing the datatype. 'SAT' value exists in the Platform column which is of varchar type and I am not trying to change the type to int.

Any help will be appreciated.

Thanks

2
sql-server
Please post the schema of RAWConsoleGames as well! - Giorgos Altanis
Maybe the column order is different in both tables... - mako
@mako exactly, but why should we try to guess? - Giorgos Altanis
@GiorgosAltanis we can only wait for him... - mako
Here is the schema for ConsoleGames (rank varchar(5000), name varchar(50), Platform varchar(50), year varchar(50), genre varchar(200), publisher varchar(1200), sales varchar(50), eu_sales varchar(50), jp_sales varchar(50), other_sales varchar(50) - Jay Emm

2 Answers

0
votes

Clearly 'SAT' is not and will never convert to an INT.

Always best to specify the columns to insert ... things change

Now, if the source data is suspect, add a try_convert(). If the conversion fails, a null value will be returned

I don't know the column names of your source, so I substituted SomeColN

INSERT INTO console_games
SELECT try_convert(integer      ,SomeCol1)
      ,try_convert(varchar(1200),SomeCol2)
      ,try_convert(varchar(1200),SomeCol3)
      ,try_convert(integer      ,SomeCol4)
      ,try_convert(varchar(200) ,SomeCol5)
      ,try_convert(varchar(1200),SomeCol6)
      ,try_convert(float        ,SomeCol7)
      ,try_convert(float        ,SomeCol8)
      ,try_convert(float        ,SomeCol9)
      ,try_convert(float        ,SomeCol10)
 FROM [dbo].[RAWConsoleGames]

Just for fun, try:

Select try_convert(int,'SAT')
Select try_convert(int,'25.25')
Select try_convert(int,'25')
0
votes

You should always define the list of columns you're inserting into, and you should also always define the list of columns you're selecting from. Furthermore, I'd recommend to explicitly do any type conversions instead of leaving that up to SQL Server - if you do it yourself, you know when and what you're doing.

So I'd write that statement like this:

-- **DEFINE** the list of columns you're inserting into
INSERT INTO console_games (rank, name, Platform, year, genre, publisher, 
                           sales, eu_sales, jp_sales, other_sales) 
    -- **DEFINE** the list of columns you're selecting, and any conversions
    SELECT     
        game_rank, game_name, platform_name, 
        CAST(game_year AS VARCHAR(50)), genre,
        publisher, 
        CAST(na_sales AS VARCHAR(50)), 
        CAST(eu_sales AS VARCHAR(50)),
        CAST(jp_sales AS VARCHAR(50)),
        CAST(other_sales AS VARCHAR(50))
    FROM 
        [dbo].[RAWConsoleGames]