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

Question

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'

i am getting this error - What error?

Alex K
– Alex K

2016-05-31 12:58:08 +00:00
Commented May 31, 2016 at 12:58 — Alex K
– Alex K, Commented May 31, 2016 at 12:58

Mattia Caputo · Accepted Answer · 2016-05-31 12:55:08Z

2

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

answered May 31, 2016 at 12:55

Mattia Caputo

9691 gold badge8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mattia Caputo Over a year ago

check my answer approved

Ragesh · Accepted Answer · 2016-05-31 13:06:21Z

0

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.

answered May 31, 2016 at 13:06

Ragesh

7383 silver badges9 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related