Johnny Bones Johnny Bones - 6 months ago 17
SQL Question

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

Some of my previous questions may now make sense... Anyway, here's what I have:

create table ##NewTemp1
(
[TableName] [nvarchar](100),
[UniqueName] [nvarchar](100),
[FieldName] [nvarchar](100),
[TransID_80079] [nvarchar](2000),
[TransID_80080] [nvarchar](2000)
)

INSERT INTO ##NewTemp1
SELECT X.TableName, X.UniqueName, X.FieldName, X.TransID_80079, X.TransID_80080
FROM (
SELECT * FROM ##Temp1175443 UNION
SELECT * FROM ##Temp2175443 UNION
SELECT * FROM ##Temp3175443 UNION
... etc ...
SELECT * FROM ##Temp22175443 UNION
SELECT * FROM ##Temp23175443 UNION
SELECT 1 AS TableName, 1 AS UniqueName, 1 AS FieldName, 1 AS TransID_80079, 1 AS TransID_80080 WHERE 1=0
) X


I'm getting this:


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


I purposely, specifically set up a temp table with nvarchar fields so that all values would be written to nvarchar fields. Why is it even mentioning an int data type? Can anyone help?

jdg jdg
Answer

Try single quotes in your last select statement:

SELECT '1' AS TableName, '1' AS UniqueName, '1' AS FieldName, '1' AS TransID_80079, '1' AS TransID_80080

Also consider dropping the WHERE 1=0 or modifying it with single quotes as well.

Those are the only instances of INT that I can see in your code.