问如何检查INSERT中哪些列的数据类型不正确？
EN

Stack Overflow用户

提问于 2018-04-15 11:47:28

回答 2查看 172关注 0票数 0

假设我在一条INSERT语句中有200列，并且偶尔会收到其中一列的"Cannot convert“错误。问题是，我不知道哪个列导致了这个错误。

在T-SQL或mybatis中有没有办法检查哪一列的格式不正确？(我只有日期、字符、数字)。我可以对每一列使用ISNUMERIC、ISDATE，但这并不是很优雅。

我在Java中使用的是mybatis，所以我不能使用任何PreparedStatement。

sql-server

tsql

mybatis

回答 2

Stack Overflow用户

发布于 2018-04-15 18:46:09

您可以构建一个尝试转换每个可疑列的查询。

并将查询限制到其中一个转换尝试失败的地方。

当尝试将坏数据转换或转换为datetime或number类型时，大多数情况下，坏数据将在CHAR或VARCHAR中。

所以你可以把你的研究局限在这些方面。

此外，从错误中，您应该可以看到哪个值未能转换为哪种类型。这也有助于限制你研究的领域。

使用表变量的简化示例：

declare @T1 table (id int identity(1,1) primary key, field1 varchar(30), field2 varchar(30), field3 varchar(30));
declare @T2 table (id int identity(1,1) primary key, field1_int int, field2_date date, field3_dec decimal(10,2));

insert into @T1 (field1, field2, field3) values
('1','2018-01-01','1.23'),
('not an int','2018-01-01','1.23'),
('1','not a date','1.23'),
('1','2018-01-01','not a decimal'),
(null,'2018-01-01','1.23'),
('1',null,'1.23'),
('1','2018-01-01',null)
;

select top 1000
id,
case when try_convert(int, field1) is null then field1 end as field1,
case when try_convert(date, field2) is null then field2 end as field2,
case when try_convert(decimal(10,4), field3) is null then field3 end as field3
from @T1
where 
   try_convert(int, coalesce(field1, '0')) is null
or try_convert(date, coalesce(field2, '1900-01-01')) is null
or try_convert(decimal(10,4), coalesce(field3, '0.0')) is null;

id  field1      field2       field3
--  ----------  -----------  -------------
2   not an int  NULL         NULL
3   NULL        not a date   NULL
4   NULL        NULL         not a decimal

如果原始数据不需要太多坏数据，您可以先尝试修复原始数据。

或者对包含错误数据的有问题的列使用try_convert。

例如：

insert into @T2 (field1_int, field2_date, field3_dec)
select 
try_convert(int, field1),
try_convert(date, field2),
try_convert(decimal(10,4), field3)
from @T1;

票数 1

Stack Overflow用户

发布于 2018-04-16 08:43:24

对于较大的导入-特别是当您预计会出现问题时-强烈建议使用两步法。

将数据导入到一个容错能力很强的临时表中(所有NVARCHAR(MAX))

check，都会评估、操作、更正所需的数据，并从此处执行真正的

这是一种通用的方法，您可以根据自己的需要进行调整。它将根据类型映射表检查所有表值并输出所有值，这些值在TRY_CAST中失败(需要SQL-Server 2012+)

一个模拟临时表的表(部分借用自LukStorms的answer - thx!)

CREATE TABLE #T1 (id INT IDENTITY(1,1) PRIMARY KEY
                 ,fldInt VARCHAR(30)
                 ,fldDate VARCHAR(30)
                 ,fldDecimal VARCHAR(30));
GO

INSERT INTO #T1 (fldInt, fldDate, fldDecimal) values
('1','2018-01-01','1.23'),
('blah','2018-01-01','1.23'),
('1','blah','1.23'),
('1','2018-01-01','blah'),
(null,'2018-01-01','1.23'),
('1',null,'1.23'),
('1','2018-01-01',null);

--类型映射(可能自动从现有目标表的INFORMATION_SCHEMA中获取)

DECLARE @type_map TABLE(ColumnName VARCHAR(100),ColumnType VARCHAR(100));
INSERT INTO @type_map VALUES('fldInt','int')
                           ,('fldDate','date')
                           ,('fldDecimal','decimal(10,2)');

--临时表的名称

DECLARE @TableName NVARCHAR(100)='#T1';

--为每列动态创建的语句

DECLARE @columnSelect NVARCHAR(MAX)=
(SELECT
    ' UNION ALL SELECT id ,''' +  tm.ColumnName + ''',''' +  tm.ColumnType + ''',' +  QUOTENAME(tm.ColumnName) 
               + ',CASE WHEN TRY_CAST(' + QUOTENAME(tm.ColumnName) + ' AS ' +  tm.ColumnType + ') IS NULL THEN 0 ELSE 1 END ' +
               'FROM ' + QUOTENAME(@TableName)
 FROM @type_map AS tm
 FOR XML PATH('')
);

-The最终动态创建的语句

DECLARE @cmd NVARCHAR(MAX)=
'SELECT tbl.*
FROM
(
    SELECT 0 AS id,'''' AS ColumnName,'''' AS ColumnType,'''' AS ColumnValue,0 AS IsValid WHERE 1=0 '
  + @columnSelect +
') AS tbl
WHERE tbl.IsValid = 0;'

--使用EXEC()执行

EXEC(@cmd);

结果是：

+----+------------+---------------+-------------+---------+
| id | ColumnName | ColumnType    | ColumnValue | IsValid |
+----+------------+---------------+-------------+---------+
| 2  | fldInt     | int           | blah        | 0       |
+----+------------+---------------+-------------+---------+
| 5  | fldInt     | int           | NULL        | 0       |
+----+------------+---------------+-------------+---------+
| 3  | fldDate    | date          | blah        | 0       |
+----+------------+---------------+-------------+---------+
| 6  | fldDate    | date          | NULL        | 0       |
+----+------------+---------------+-------------+---------+
| 4  | fldDecimal | decimal(10,2) | blah        | 0       |
+----+------------+---------------+-------------+---------+
| 7  | fldDecimal | decimal(10,2) | NULL        | 0       |
+----+------------+---------------+-------------+---------+

创建的语句如下所示：

SELECT tbl.*
FROM
(
    SELECT 0 AS id,'' AS ColumnName,'' AS ColumnType,'' AS ColumnValue,0 AS IsValid WHERE 1=0 
    UNION ALL SELECT id 
                    ,'fldInt'
                    ,'int'
                    ,[fldInt]
                    ,CASE WHEN TRY_CAST([fldInt] AS int) IS NULL THEN 0 ELSE 1 END 
              FROM [#T1]
    UNION ALL SELECT id 
                    ,'fldDate'
                    ,'date',[fldDate]
                    ,CASE WHEN TRY_CAST([fldDate] AS date) IS NULL THEN 0 ELSE 1 END 
              FROM [#T1]
    UNION ALL SELECT id 
                   ,'fldDecimal'
                   ,'decimal(10,2)'
                   ,[fldDecimal]
                   ,CASE WHEN TRY_CAST([fldDecimal] AS decimal(10,2)) IS NULL THEN 0 ELSE 1 END 
              FROM [#T1]
) AS tbl
WHERE tbl.IsValid = 0;