CODEWITHSUNDEEP
sql-server-2008
Ravi Shukla
Ravi Shukla

Reputation: 73

Conversion failed when converting the varchar value to data type int in sql server 2008 new

I am getting this error when I am running this query.

I know this is not right but I did, and I just want to am justified answer about it.

Why this error is coming when we are running this below code.

DECLARE @tbl TABLE ( id INT, Name VARCHAR(5) );

INSERT INTO @tbl
        SELECT 1 AS Id, 142 AS Alphabets
        UNION
        SELECT 2, 'XYZ'
        UNION 
        SELECT 2, 'ABC'
        SELECT * FROM @tbl

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

Upvotes: 2

Views: 3618

Answers (2)

Ragesh
Ragesh

Reputation: 738

In UNION operator : number,order and data type of columns must be same in all queries.

Refer this link for more info : https://msdn.microsoft.com/en-IN/library/ms180026.aspx

Here in your query, data type of 2nd column of 1st select query is different from other union select queries 2nd column.

Thats why you must have got conversion failed error when converting the varchar value to data type int.

Upvotes: 0

Mattia Caputo
Mattia Caputo

Reputation: 969

the problem is 142.
When you write like this 142,
SQL think this union table at second column contain a INT.
you have to write like this '142' 

DECLARE @tbl TABLE (id INT, Name VARCHAR(5));

INSERT INTO @tbl
SELECT 1 AS Id, '142' AS Alphabets
UNION
SELECT 2, 'XYZ'
UNION 
SELECT 2, 'ABC'

SELECT * FROM @tbl

Upvotes: 2

Related Questions