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