August 14, 2014 at 11:38 am
Hi,
I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
My problem is that Line 6 is:
set @brn_pk = '0D4BDE66347C440F'
so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. Any ideas how I can work out what column is causing the problem?
August 14, 2014 at 11:41 am
I would follow the binary tree approach. Comment out fist 100 fields, then 50 .............................
If the column names are similar in both the tables, make a join and figure out what columns are not matching.
select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS IC where TABLE_NAME = 'TableName'
August 14, 2014 at 11:50 am
Thanks, the binary tree approach is what I am using but was really hoping there would have been a faster method.
August 14, 2014 at 12:18 pm
Quick thought along with previous suggestion, selecting with a conversion will still return the rows up to the conversion failure, here is an example code
😎
USE tempdb;
GO
CREATE TABLE dbo.TBL_CONVERSION_TEST_SOURCE
(
CT_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,CT_INT INT NOT NULL
,CT_DATETIME DATETIME NOT NULL DEFAULT(GETDATE())
,CT_VARCHAR VARCHAR(50) NOT NULL
);
CREATE TABLE dbo.TBL_CONVERSION_TEST_DESTINATION
(
CT_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,CT_INT INT NOT NULL
,CT_DATETIME DATETIME NOT NULL DEFAULT(GETDATE())
,CT_VARCHAR INT NOT NULL
);
INSERT INTO dbo.TBL_CONVERSION_TEST_SOURCE(CT_INT,CT_VARCHAR)
VALUES
(1,'00001')
,(2,'00002')
,(3,'0000B')
,(4,'00004');
DECLARE @SQL_STR NVARCHAR(MAX) = N''
SELECT @SQL_STR = N'SELECT ' +STUFF((
SELECT
N',CAST(' + C.COLUMN_NAME +N' AS ' + DATA_TYPE + N') AS ' + C.COLUMN_NAME + NCHAR(13) + NCHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = N'TBL_CONVERSION_TEST_DESTINATION'
AND TABLE_SCHEMA = N'dbo'
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)'),1,1,'')
+ N' FROM dbo.TBL_CONVERSION_TEST_SOURCE;'
EXEC (@SQL_STR);
Results in an error message
Msg 245, Level 16, State 1, Line 26
Conversion failed when converting the varchar value '0000B' to data type int.
and a partial result set
CT_ID CT_INT CT_DATETIME CT_VARCHAR
----------- ----------- ----------------------- -----------
1 1 2014-08-14 19:08:20.120 1
2 2 2014-08-14 19:08:20.120 2
The culprit is then CT_ID = 3
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply